SQL Server log file is too big – resolved!

If your SQL Server transaction log (LDF) file is too big – you are doing something wrong. As technet puts it:

Typically, truncation occurs automatically under the simple recovery model when database is backed up and under the full recovery model when the transaction log is backed up. However, truncation can be delayed by a number of factors. For more information, see Factors That Can Delay Log Truncation.

However in our experience, the most common problem is that user sets recovery model to full, and then forgets to do transaction log backups. The solution is simple – switch to the simple recovery model, or start backing up your  logs with tools like SqlBak or  SQLBackupAndFTP

SQL Server is smart in selecting great default options for a new database. If you can afford to lose the changes between backups to keep maintenance simple (in most non-critical databases it is a reasonable compromise)  and you backup your database regularly, then the simple recovery model selected by default is appropriate for you.  However even in this case if you ever had massive data operations, you may notice that the size of your transaction log (LDF) file is huge. The reason for it is that SQL server does not automatically shrinks the size of transaction log.

To keep log file under control, it may be tempting to enable Auto Shrink option. That would be a mistake – it is an evil option that should always be off. Shrink is a heavy procedure that should only be used rarely.

Instead just run this command to decrease the size of the data and log files to leave 10 percent free space (read more):

DBCC SHRINKDATABASE (YourDatabaseName, 10)

For more precise operation use DBCC SHRINKFILE or you can just do it in Management Studio

It is not over yet! Most often shrink will increase fragmentation of your indexes. To defragment indexes, you’ll need to use ALTER DATABASE – see this link

Leave a Comment