Tuesday, April 26, 2011

Stop Database Transaction Log files from growing too large

This method applies to any database that uses SQL Server Management Studio (including SQL Server and Windows Internal databases).

Transaction log files are by default allowed to grow automatically and if left unchecked these files can grow to be tens of gigabytes in size. If you don't have a lot of allocated space it can be quite nerve racking to learn a single transaction log has eaten up all your available space.

To stop database log files from growing too large and later having to truncate them you can set a Maximum File Size or even turn of Autogrowth per database. To set these attributes:

  1. Open SQL Server Management Studio and connect to your database server
  2. Right click on the database you want to adjust and click Properties
  3. Click the Files page and in the table there should be a column called Autogrowth. 
  4. Select the row for the file you want to stop growing (like the log file) and click the ellipsis (the ...) 
  5. The Change Autogrowth window will appear
  6. From here you can Turn off Autogrowth, adjust how quickly file growth is allowed or set a restricted file growth in MB (which is what I recommend)
This post is a follow up to my prior post on How to Compact or Shrink a Windows SharePoint Services 3.0 Databas. It also applies to shrinking transaction log files in SQL Server or Windows Internal Database. 

No comments: