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

By | In Art of BI, SQL Server | May 22nd, 2009

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 a 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.

[sourcecode language=’sql’]
USE GLReporting
GO
DBCC SHRINKFILE(GLReporting_log, 200)
BACKUP LOG GLReporting WITH TRUNCATE_ONLY
DBCC SHRINKFILE(GLReporting_log, 200)
GO
[/sourcecode]

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”.
database_properties

In the left hand menu of the new prompt select “Files”. In right-hand side of the 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 elipse for the log file row in question.
database_properires_autogrowth1

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.
autogrowth_properties

So, let’s change that value to something more managable 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 that 10GB log space ever. Click “OK” at all of the prompts and you are finished.

Contact Us
Christian Screen
Christian is an innovator in analytics and data warehousing design, best practices, and delivery. With more than fifteenyears of decision support and data warehousing with key experiences at Office Depot HQ, Sierra-Cedar, and Capgemini, he oversees the Oracle Analytics Practice which includes the technical development and delivery of Oracle BI collaboration software, data warehouse solutions, Oracle BI/EPM projects, and packaged analytics solutions at Datavail.

Leave a Reply

Your email address will not be published.
Required fields are marked (*).

1 thought on “Art of BI: SQL Server – Truncate and Shrink Log File”