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