Transaction Log Size

[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.]

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]

Vitaliy,

Database is set to simple mode, should I manually set a size to shrink the log file to ?

[Vitaliy S.]

Yes, please select Tasks > Shrink > Database and leave all parameters as defaults.

[SWICT]

Not sure it has worked as log file is still 160+GB ?

[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]

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]

Hi,

Results from the SQl queries:

log size = 171431.3
Space Used = 91.30241
Status = 0

VeeamBackup SIMPLEREPLICATION

Thanks

[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]

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]

Update:

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

Thanks

[alexander sh]

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