Art of BI: SQL Server – Truncate and Shrink Log File

Often when building a database, not all properties and settings are configured properly when initially created. One configuration property that we see left dangling the most is the default log file growth property of each database. The default allows a database log file to grow to a substantially large file size if not restricted. Often this file can leave your SQL Server box with low disk space errors and can crash SQL Server (prevent it from starting, etc.).

One quick and dirty way to alleviate a bloated log file’s size is to run the command below in a new query window.

In the example below “GLReporting” is the name of the database in question and “GLReporting_log” is the alias name of the physical log file “GLReporting_Log.LDF”. Notice that the first parameter of the DBCC SHRINKFILE… command does not contain the “.LDF” file extension.


USE GLReporting
GO
DBCC SHRINKFILE(GLReporting_log, 200)
BACKUP LOG GLReporting WITH TRUNCATE_ONLY
DBCC SHRINKFILE(GLReporting_log, 200)
GO


Further Important Information

As a side note to the above quick and dirty script, the best practice would be to prevent the database log file from growing out of control in the first place. To do this you must configure the log file growth restriction property. To accomplish this run the SQL Server Management Studio application, connect to your DB server, and open the Object Explorer.

Expand your database folder list. Right-click on the database in question and select “Properties”.

In the left-hand menu of the new prompt select “Files”. In the right-hand side of the window prompt find the log file in question (sort by the File Type column if it helps you). Under the “Authgrowth” column click on the ellipse for the log file row in question.

Under the “Maximum File Size” section ensure that “Restricted File Growth(MB)” is selected. The default setting for this property is 2,097,152 MB which is roughly 2 TeraBytes if my math is right.

So, let’s change that value to something more manageable like 2,152 which gives us a max size of 2GB. If that is too low for you data then bump it up to something like 10,152 (10GB). Most basic databases shouldn’t need more than 10GB log space ever. Click “OK” at all of the prompts and you are finished.