How Transaction Log Truncation and Growth Works in SQL Server 2005

I have been seeing a lot of questions in the SQL Server Database Engine MSDN Forum about how and why transaction logs grow and what can be done about it.

First of all you need to know what recovery model you are using for a database. The choices are Simple, Full and Bulk-logged.  You can find out what model you are using by running this query:

-- Get recovery model for each database
    SELECT [name], recovery_model_desc
    FROM sys.databases

If you are running with Simple, the log file should not normally fill up and grow unless you have a problem that is preventing the log from being truncated internally (which normally happens whenever the system issues a checkpoint in Simple mode).  You can find out whether the transaction log is “filling up” and why by running the two queries below:

-- Individual File Size query
    SELECT name AS 'File Name' , physical_name AS 'Physical Name', size/128 AS 'Total Size in MB',
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB'--, *
    FROM sys.database_files;

-- Get log reuse wait description
    SELECT [name], log_reuse_wait_desc 
    FROM sys.databases

The most common reason for this is a long running, active transaction. The log_reuse_desc will be ACTIVE_TRANSACTION if this is the case. If so, you can run the queries below to find the culprit:

-- Find oldest transaction
    DBCC OPENTRAN

-- Get input buffer for a SPID
    DBCC INPUTBUFFER(21) – Substitute the SPID number from above

At any rate, if something is keeping the log file from being reused (the log_reuse_desc is something besides NOTHING), the log will eventually fill up. If you have AutoGrow enabled for the transaction log file, it will autogrow the file until the disk eventually runs out of space. If this happens, you have a read-only database until you correct the problem.

If you are running in Full Recovery model, you must periodically backup the transaction log and truncate it, or else the transaction log will fill up and grow. How often you need to do that will depend on how much data is written to your database and how big your transaction log file is. You also need to think about your business requirements, and how much data (timewise) you could afford to lose.

If your transaction log file has grown excessively large, you can shrink it by running DBCC SHRINKFILE, but I would not do that unless you really need the disk space, since shrinking the log file can heavily fragment it. The command below shows you how to shrink the log file.

DBCC SHRINKFILE (N'AdventureWorks_Log' , 0, TRUNCATEONLY)
GO
 
Technorati Tags:

This entry was posted in SQL Server 2005. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s