SQL Server Runaway Transaction Logs
When SQL Server is used without a qualified DBA, runaway transaction logs are by far the most common problem. This behavior is because the default configuration of SQL Server is intended to support production databases, and production databases should normally have their transaction logs backed up on a regular basis. If the transaction logs are regularly backed up, they will not be in a runaway configuration.
A runaway transaction log occurs when a database is writable, is in the full recovery model, the transaction log is set to autogrow, and the transaction log has not been backed up recently. This is because the full recovery model does not allow any portion of a transaction log to be reused until it has been backed up, and therefore the transaction log will continue to grow until the drive is full. How long it takes to fill up a hard drive depends on the size of the hard drive, the number of transactions, and the average transaction size.
Two of the first things a trained SQL Server database administrator (DBA) will do is to create a comprehensive backup plan that includes the transaction logs for production databases, and change the recovery model from full to simple for development databases. Those actions prevent a runaway transaction log.
If you're an involuntary DBA (e.g. a system administrator who has been tasked with managing SQL Server), you should check each one of your databases for a runaway log configuration. All you have to do is adjust the number of days in the DATEADD function in the query below and then run it, and it will tell you name of each database that needs to be reconfigured.
You can resolve a runaway log condition by backing up the transaction log or by changing a database's recovery model to simple. Changing to simple recovery model is recommended if you don't care if recent data changes are lost, such as most databases used for development purposes. A comprehensive backup schedule that includes the transaction logs is recommended if no data loss is an important goal, such as for most production databases.
***********************************************************************************************
ADJUSTING THE TIME PERIOD
How frequently the transaction logs need to be backed up for a production database depends on the size of the hard drive, the total number of databases, the number of transactions for each database, and the average transaction size for each database. The query below will identify a database as being in a runaway log configuration if it is in the full recovery model, the log file is set to autogrow, and the tranaction log has not been backed up in the last 7 days.
The time period used in they query below is in this DATEADD function:
DATEADD(d,-7,GETDATE())
Increase the number 7 in the query below to a larger number if you have a small, inactive database. Decrease the number 7 to a smaller number if you have a large, active database. You can also change the "d" in the DATEADD function to "hh" to measure by hours instead of days.
***********************************************************************************************
Additonal Notes:
- A full backup must be performed before a transaction log backup is possible.
- A full backup of a database does not backup the transaction log and will not prevent a runaway log file. A full backup covers the .MDF file, and a transaction log backup covers the .LDF file. Both need to be backed up for a normal production database.
- You can also avoid a runaway log configuration by disabling autogrowth for the log file, but that's usually a bad idea because if the log file becomes full and is not allowed to grow, the database will effectively become read-only until the log file can be reused or expanded.
- The model database is the template that is copied when new databases are created. Don't change the recovery model of the model database unless you want to change the default recovery model for all new databases.
- For SQL Server Express, the model database is set to the Simple recovery model by default, because it does not expect a DBA to manage it.
***********************************************************************************************
--List databases within an instance of SQL Server if they are in a runaway transaction log configuration.
SELECT d.name as [Database with runaway log configuration]
FROM master.sys.databases d
INNER JOIN (--databases with transaction log files set to autogrow
SELECT db_name(database_id) as name FROM master.sys.master_files WHERE type_desc = 'LOG' AND growth > 0
) a ON d.name = a.name
LEFT JOIN (--recent log backups
SELECT database_name, type
FROM msdb.dbo.backupset
WHERE type = 'L' AND backup_start_date > DATEADD(d,-7,GETDATE())
) s ON d.name = s.database_name
WHERE d.recovery_model_desc = 'FULL' AND s.type IS NULL
***********************************************************************************************
By the way, I'm not tagging this blog article as "off-topic" because backups are a standard security requirement, as it affects data availability.
Additional search phrases: full hard drive, hard drive is full, hard drive is filled up, transactional log too large.