Host-based backup of Microsoft Hyper-V VMs.
Post Reply
SWICT
Influencer
Posts: 10
Liked: 1 time
Joined: Apr 22, 2015 9:14 am
Full Name: Tim Fountain
Contact:

Transaction Log Size

Post by SWICT »

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
Vitaliy S.
VP, Product Management
Posts: 27055
Liked: 2710 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: Transaction Log Size

Post by Vitaliy S. »

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?
SWICT
Influencer
Posts: 10
Liked: 1 time
Joined: Apr 22, 2015 9:14 am
Full Name: Tim Fountain
Contact:

Re: Transaction Log Size

Post by SWICT »

Vitaliy,

Database is set to simple mode, should I manually set a size to shrink the log file to ?
Vitaliy S.
VP, Product Management
Posts: 27055
Liked: 2710 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: Transaction Log Size

Post by Vitaliy S. »

Yes, please select Tasks > Shrink > Database and leave all parameters as defaults.
SWICT
Influencer
Posts: 10
Liked: 1 time
Joined: Apr 22, 2015 9:14 am
Full Name: Tim Fountain
Contact:

Re: Transaction Log Size

Post by SWICT »

Not sure it has worked as log file is still 160+GB ?
Vitaliy S.
VP, Product Management
Posts: 27055
Liked: 2710 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: Transaction Log Size

Post by Vitaliy S. »

Yes, seems like it didn't work. Can you let our support engineers take a look at your database via WebEx session?
alexander sh
Veeam Software
Posts: 40
Liked: 28 times
Joined: Dec 13, 2012 8:52 am
Full Name: Alexander Shelemin
Location: Prague
Contact:

Re: Transaction Log Size

Post by alexander sh » 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'
SWICT
Influencer
Posts: 10
Liked: 1 time
Joined: Apr 22, 2015 9:14 am
Full Name: Tim Fountain
Contact:

Re: Transaction Log Size

Post by SWICT »

Hi,

Results from the SQl queries:

log size = 171431.3
Space Used = 91.30241
Status = 0

VeeamBackup SIMPLEREPLICATION

Thanks
alexander sh
Veeam Software
Posts: 40
Liked: 28 times
Joined: Dec 13, 2012 8:52 am
Full Name: Alexander Shelemin
Location: Prague
Contact:

Re: Transaction Log Size

Post by alexander sh »

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.
SWICT
Influencer
Posts: 10
Liked: 1 time
Joined: Apr 22, 2015 9:14 am
Full Name: Tim Fountain
Contact:

Re: Transaction Log Size

Post by SWICT »

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: Apr 22, 2015 9:14 am
Full Name: Tim Fountain
Contact:

Re: Transaction Log Size

Post by SWICT » 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
alexander sh
Veeam Software
Posts: 40
Liked: 28 times
Joined: Dec 13, 2012 8:52 am
Full Name: Alexander Shelemin
Location: Prague
Contact:

Re: Transaction Log Size

Post by alexander sh »

Thanks for letting us know, glad to hear it did the trick for you!
Post Reply

Who is online

Users browsing this forum: Matthias.Mehrtens and 34 guests