Select Page

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

Christian Screen | | May 22, 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 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”.

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

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

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Art of BI: BI Publisher (BIP) Quick Guide and Tips

Read our blog post on how to take over production support of BI Publisher reports.

Sherry Milad | January 15, 2018

How to Index a Fact Table – A Best Practice

At the base of any good BI project is a solid data warehouse or data mart.

Christian Screen | March 16, 2010

Art of BI: How to Add Comments in Oracle BI (OBIEE)

Ultimately the goal of commentary in OBIEE is to have a system for persisting feedback, creating a call to action, and recognizing the prolific users.

Christian Screen | December 29, 2013

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS