Select Page

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

Author: 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
DBCC SHRINKFILE(GLReporting_log, 200)
DBCC SHRINKFILE(GLReporting_log, 200)

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.

Oracle BI Publisher (BIP) Tips: Functions, Calculations & More

Check out these BI Publisher tips including functions & calculations so you can understand more about the production and 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

Qlik vs. Tableau vs. Power BI: Which BI Tool Is Right for You?

Tableau, Power BI, and Qlik each have their benefits. What are they and how do you choose? Read this blog post for a quick analysis.

Tom Hoblitzell | June 6, 2019

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.

Work with Us

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


Work for Us

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