How to fix large SQL Server transaction log file?

It is Huge

It is huge, it is thick as hell, and you want to get rid of it? If you are thinking about transaction log file (I hope you are 😉 ) I will show you how to do it.

First thing is to check which database is using it. If you have small SQL Server than it is easy to find to which Database it belongs (In my case it was VMware vCenter RSA Database). If you have a large amount of databases probably it will be easier to use T-SQL http://msdn.microsoft.com/en-us/library/ms189493.aspx.

In order to shrink translog.ldf using SQL Server Management Studio and go to:

  • Databases
  • Select desired Database and click right mouse button
  • Select Tasks Shrink Files
  • Choose File type as Log and click OK.

In my case translog was 38GB and after shrinking it was only 10MB. My mistake when I was creating RSA Database I didn’t select Recovery model simple.