Thursday, April 15

Shrink .ldf file of MS SQL Server

.ldf files are the transaction log of database. By default it is created with "unrestricted growth" option, so it can be much bigger than the actual database file.

With these commands you can shrink .ldf file to its initial size:
backup log databasename with truncate only; --Mark inactive entries in the log file to be truncated.
use databasename;
dbcc shrinkfile (databasename_log); --shrink.
But you still need to do something to prevent it from growing unrestricted again. You can decide a specific number (2 Gig?) for this file (In Database Properties->Files interface), but that is not ideal.

By default a database is created with "Full Recovery" mode. That means, if you have a database failure, you restore the database from yesterday's backup tape, then with all the transaction logs, you can restore the database to older state. Most people keep 2 week's daily backup, so the transaction log is never used. In this situation "Simple Recovery" mode can be applied. The database will truncate all the transaction log every time database is being backup. That means, if you have daily backup, the transaction log (.ldf file) is truncated to initial size every day.

To set the "Simple Recovery" mode, go to the Database Properties and select "Option":

Please visit Microsoft link How to stop the transaction log of a SQL Server database from growing unexpectedly to get detail info of this topic.