Big ldf file.

[Akopylov]

Hello there.

I got MS SQL Server 2008R2 (well, appwiz.cpl says so, however if I remember it right, R3 update was installed, but I don`t know how to figure it out), Windows Server 2012 R2.

I really newb in SQL and use it only as vCenter database engine (usually I use vapp with SLES, but this time it is Windows). So I figured out that the ldf file is pretty big (~28GB) while the database is about 4 GB. Recovery model is set to Full (I guess it should be Simple?). I need advice how to shrink the ldf file without any impact or corruption of vCenter database and how to avoid such problems in the future.

P.S. AFAIK the ldf file with such recovery model should grow only if the backup of the database is not performed. In my case Veeam B&R is set to copy transaction files (copy only, not the "process transaction logs with this job"), so I guess it is not enough to "force" MS SQL to "think" that backup of the database is performed?

[rcporto · Accepted solution]

Unless I'm missing something there is not R3 for SQL Server 2008, maybe you're talking about SP3 that can be applied to a SQL Server 2008 R2.

About the transaction log, take a look at the following VMware KB article to see how to shrink the transaction log: "The transaction log for database 'VIM_VCDB' is full" error on a Microsoft SQL DB server (1003980) |...

And to avoid shrink your transaction log manually each time it grow and don't use any other software to backup your SQL, configure the Veeam to process the transaction logs:

https://helpcenter.veeam.com/backup/vsphere/transaction_truncation.html

https://helpcenter.veeam.com/backup/vsphere/copy_only.html