Transaction Log Size

Hyper-V specific discussions

Transaction Log Size

Veeam Logoby SWICT » Mon Jul 20, 2015 1:10 pm

Hi,

I am looking to migrate our current local SQL 2008 Express B&R v8 database to our full SQL 2012 server.

The transaction log file for B&R is 160GB, is this normal size ? We backup approx 100 Hyper-V virtuals.

Thanks
Tim
SWICT
Influencer
 
Posts: 10
Liked: 1 time
Joined: Wed Apr 22, 2015 9:14 am
Full Name: Tim Fountain

Re: Transaction Log Size

Veeam Logoby Vitaliy S. » Mon Jul 20, 2015 1:56 pm

Veeam B&R should be in simple recovery mode, thus the transaction log shouldn't grow that big. Can you please check Veeam B&R database configuration and try to shrink this log via SQL Management Studio manually?
Vitaliy S.
Veeam Software
 
Posts: 19564
Liked: 1104 times
Joined: Mon Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov

Re: Transaction Log Size

Veeam Logoby SWICT » Mon Jul 20, 2015 3:07 pm

Vitaliy,

Database is set to simple mode, should I manually set a size to shrink the log file to ?
SWICT
Influencer
 
Posts: 10
Liked: 1 time
Joined: Wed Apr 22, 2015 9:14 am
Full Name: Tim Fountain

Re: Transaction Log Size

Veeam Logoby Vitaliy S. » Mon Jul 20, 2015 3:34 pm

Yes, please select Tasks > Shrink > Database and leave all parameters as defaults.
Vitaliy S.
Veeam Software
 
Posts: 19564
Liked: 1104 times
Joined: Mon Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov

Re: Transaction Log Size

Veeam Logoby SWICT » Mon Jul 20, 2015 3:41 pm

Not sure it has worked as log file is still 160+GB ?
SWICT
Influencer
 
Posts: 10
Liked: 1 time
Joined: Wed Apr 22, 2015 9:14 am
Full Name: Tim Fountain

Re: Transaction Log Size

Veeam Logoby Vitaliy S. » Mon Jul 20, 2015 4:59 pm

Yes, seems like it didn't work. Can you let our support engineers take a look at your database via WebEx session?
Vitaliy S.
Veeam Software
 
Posts: 19564
Liked: 1104 times
Joined: Mon Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov

Re: Transaction Log Size

Veeam Logoby alexander sh » Mon Jul 20, 2015 5:28 pm 1 person likes this post

Could you please run the following two statements in SQL Management Studio and post the results here:

Code: Select all
dbcc sqlperf(logspace)

Code: Select all
select name, recovery_model_desc, log_reuse_wait_desc from sys.databases where name = 'VeeamBackup'
alexander sh
Veeam Software
 
Posts: 23
Liked: 16 times
Joined: Thu Dec 13, 2012 8:52 am
Full Name: Alexander Shelemin

Re: Transaction Log Size

Veeam Logoby SWICT » Tue Jul 21, 2015 9:18 am

Hi,

Results from the SQl queries:

log size = 171431.3
Space Used = 91.30241
Status = 0

VeeamBackup SIMPLEREPLICATION

Thanks
SWICT
Influencer
 
Posts: 10
Liked: 1 time
Joined: Wed Apr 22, 2015 9:14 am
Full Name: Tim Fountain

Re: Transaction Log Size

Veeam Logoby alexander sh » Tue Jul 21, 2015 9:52 am

Thanks for running the SQL commands, they shed some light.
This is something I've stumbled upon before - there's a SQL Server issue when under rare circumstances a database may be marked as a part of Database Replication, even when there's no Database Replication set up on the server (it seems it usually happens during a restore process). In this case, the database will wait for replication to occur and not truncate the log, causing it to grow.

Please run the following to remove the database from replication:

EXEC sp_removedbreplication VeeamBackup

Then rerun the two SQL commands from yesterday - you should now see that "Space used" is around 0-1% and log_reuse_wait_desc ideally should say "Nothing". If that's the case, you should be then able shrink the log file.
alexander sh
Veeam Software
 
Posts: 23
Liked: 16 times
Joined: Thu Dec 13, 2012 8:52 am
Full Name: Alexander Shelemin

Re: Transaction Log Size

Veeam Logoby SWICT » Tue Jul 21, 2015 10:43 am

Ran the command which was successful.
Ran the shrink database task and got this error.

Code: Select all
TITLE: Microsoft SQL Server Management Studio
------------------------------

Shrink failed for Database 'VeeamBackup'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.2500.0+((KJ_PCU_Main).110617-0038+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Shrink+Database&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Transaction (Process ID 70) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Transaction (Process ID 70) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
The statement has been terminated.
The statement has been terminated.
DBCC execution completed. If DBCC printed error messages, contact your system administrator. (Microsoft SQL Server, Error: 1205)
SWICT
Influencer
 
Posts: 10
Liked: 1 time
Joined: Wed Apr 22, 2015 9:14 am
Full Name: Tim Fountain

Re: Transaction Log Size

Veeam Logoby SWICT » Tue Jul 21, 2015 11:01 am 1 person likes this post

Update:

I have just reloaded sql and the database is now showing as 300mb, so it appears that the shrink worked.

Thanks
SWICT
Influencer
 
Posts: 10
Liked: 1 time
Joined: Wed Apr 22, 2015 9:14 am
Full Name: Tim Fountain

Re: Transaction Log Size

Veeam Logoby alexander sh » Tue Jul 21, 2015 1:43 pm

Thanks for letting us know, glad to hear it did the trick for you!
alexander sh
Veeam Software
 
Posts: 23
Liked: 16 times
Joined: Thu Dec 13, 2012 8:52 am
Full Name: Alexander Shelemin


Return to Microsoft Hyper-V



Who is online

Users browsing this forum: No registered users and 9 guests