You've just become responsible for a database, only to find that the log file is growing out of control. Why is it happening and what do you do to correct it?
Consider a situation where, as a DBA, a new database falls under your care. Having implemented some monitoring, you find that the log has grown massively in size, is almost full, and that there isn't the capacity on its disk drive to accommodate an urgent database maintenance operation.
What next? The reaction to avoid is panic, with resort to one of the many quick but dangerous ways to empty the log file, that appear on various online forums. We need to find a way to allow the maintenance process to proceed, we need to find out why the log grew so large and fix the issue and then we need to reduce the size of the log, in the process removing any internal fragmentation. We need to do so in a way that preserves the integrity of the log chain, which we may need subsequently for a database restore operation.
Explosive Log Growth
For demonstration purposes, we'll create a simple
MyMessages database, as shown in Listing 1. Notice that we specify a small initial log file (2 MB), which is set to grow in small increments (2 MB). We set the database to
FULL recovery model and perform a full database backup (remember that
FULL recovery model becomes operation only after the first full database backup). Finally, we create a simple
USE master; GO IF DB_ID('MyMessages') IS NOT NULL DROP DATABASE MyMessages; GO CREATE DATABASE MyMessages ON PRIMARY ( NAME = N'MyMessages' , FILENAME = N'C:\SQLData\MyMessages.mdf' , SIZE = 199680KB , FILEGROWTH = 16384KB ) LOG ON ( NAME = N'MyMessages_log' , FILENAME = N'D:\SQLData\MyMessages.ldf' , SIZE = 2048KB , FILEGROWTH = 2048KB ); GO ALTER DATABASE MyMessages SET RECOVERY FULL; GO USE master; GO BACKUP DATABASE MyMessages TO DISK ='D:\SQLBackups\MyMessages_full.bak' WITH INIT; GO USE MyMessages; GO CREATE TABLE dbo.Messages ( MessageText nvarchar(200) NOT NULL , MessageDate datetime2 NOT NULL ); GO
Next, we'll simulate some transactional activity on the Messages table, by inserting a row into the Messages table and then updating it in an explicit transaction, without committing or rolling back. Open a new tab in SSMS and execute Listing 2.
USE MyMessages; GO INSERT INTO dbo.Messages ( MessageText , MessageDate ) VALUES ( 'A first message' , '2013-02-26 17:54:47' ); GO BEGIN TRANSACTION UPDATE dbo.Messages SET MessageText = 'A newer message' -- ROLLBACK TRANSACTION
Back in the original tab, we'll proceed to insert 1.3 million rows into
USE MyMessages; GO INSERT INTO Messages ( MessageText, MessageDate ) SELECT TOP 1000000 REPLICATE('a', 200) , GETDATE() FROM msdb.sys.columns a CROSS JOIN msdb.sys.columns b; GO -- repeat above to add another 300K rows
Let's assume, obviously without any prior knowledge of what's gone before, that this is the point that the DBA receives news of the problem. From log size and space usage data, we find that the log file for the
MyMessages database is 836 MB in size and close to full.
DBCC SQLPERF(LOGSPACE); -- MyMessages Log Size: 836 MB, Log Space Used: 86%
Making Room in the Log
The first priority is to try to make some room in the log so that it doesn't fill up completely, and so that the maintenance process can proceed. We try a log backup but for reasons that require further investigation, SQL Server will not truncate the log.
BACKUP LOG MyMessages TO DISK = 'D:\SQLBackups\MyMessages_log1.trn' WITH INIT; GO DBCC SQLPERF(LOGSPACE); -- MyMessages Log Size: 836 MB, Log Space Used: 86%
In order to buy some time, and stave off further problems, we decide to create more space in the log by adding a 3 GB secondary log file, on a separate disk.
USE master; GO ALTER DATABASE MyMessages ADD LOG FILE ( NAME = N'MyMessages_log2', FILENAME = N'D:\SQLData\MyMessages2.ldf' , SIZE = 512000KB , FILEGROWTH = 512000KB ); GO USE master GO ALTER DATABASE MyMessages MODIFY FILE ( NAME = N'MyMessages_log2', SIZE = 3146752KB ); GO DBCC SQLPERF(LOGSPACE); -- MyMessages Log Size: 3909 MB, Log Space Used: 14.5%
The planned database maintenance operation can now proceed and we can turn our attention to finding out what caused the log to grow so large, and why SQL Server won't truncate it.
Why doesn't SQL Server truncate the log?
BULK_LOGGED recovery model database, only a log backup will result in truncation of the log (i.e. enable reuse of space in the log). When a log backup occurs, SQL Server can reuse the space in any portions of the log that are marked as "inactive". The oldest log record (referred to as the
MinLSN record) that is still required for a successful database wide rollback, or is still required by another activity or operation in the database marks the start of the active log. The MinLSN log record could be the one marking the start of the oldest open transaction, or the oldest log record still required for a log backup, or the oldest log record still required by another database process, such as database mirroring or transactional replication (see http://www.sqlservercentral.com/articles/Transaction+Logs/72488/ for more details).
This explains why a long-running uncommitted transaction or an application leaving "orphaned transactions" in the database can keep large areas of the log "active" and so prevent log truncation. In this case, we've contrived to leave an uncommitted transaction in the database and so can simply commit it or roll it back. To simulate this, return to listing 2, and roll back the open transaction.
Next, we can take another log backup and it truncates the log, and creates plenty of reusable space in the primary log file.
BACKUP LOG MyMessages TO DISK = 'D:\SQLBackups\MyMessages_log2.trn' WITH INIT; GO DBCC SQLPERF(LOGSPACE); -- MyMessages Log Size: 3909 MB, Log Space Used: 1.4%
So far so good: we've allowed the maintenance operation to proceed, and we've fixed the problem that caused the explosive log growth. However, we still have two outstanding problems:
- A database with multiple log files
- A database with a principal log file that is bloated and likely highly fragmented
Remove any Secondary log files
We need to get rid of that secondary log file as soon as possible. SQL Server does not write log records in parallel to multiple log files, even when created on separate drives, so there is no performance advantage to having multiple log files. As soon as an "emergency" secondary log file is no longer required, we need to remove it, as all it will do is slow down any restore or recovery operations, since SQL Server has to zero-initialize the log during full and differential restore operations.
Instant file initialization and log files
The instant file initialization feature, enabled by assigning the
'Perform volume maintenance tasks' privilege to the SQL Server service account, applies only to data files, for reasons explained by Paul Randal, here:
To see the impact this can have, let's leave our secondary log file in place and perform a restore on the
USE master; go RESTORE DATABASE MyMessages FROM DISK ='D:\SQLBackups\MyMessages_full.bak' WITH REPLACE, NORECOVERY; RESTORE DATABASE MyMessages FROM DISK='D:\SQLBackups\MyMessages_log1.trn' WITH NORECOVERY; RESTORE DATABASE MyMessages FROM DISK='D:\SQLBackups\MyMessages_log2.trn' WITH RECOVERY; GO /*Processed 89539 pages for database 'MyMessages', file 'MyMessages_log' on file 1. RESTORE LOG successfully processed 89539 pages in 24.409 seconds (28.658 MB/sec). Processed 0 pages for database 'MyMessages', file 'MyMessages' on file 1. Processed 3 pages for database 'MyMessages', file 'MyMessages_log' on file 1. Processed 0 pages for database 'MyMessages', file 'MyMessages_log2' on file 1. RESTORE LOG successfully processed 3 pages in 37.085 seconds (0.000 MB/sec).*/
The restore took over 50 s. If we repeat the exact same steps, but without adding the secondary log file, the comparative restore, in our tests, took about 6 seconds. This is a substantial impact, even for a relatively modestly size secondary log. In cases where the log file is much larger, the effect on backup times can be dramatic.
In order to remove the secondary log file, we need to wait until it contains no part of the active log. Since our goal is to remove it, it's permissible to shrink this secondary log file (demonstrated shortly), and turn off auto-growth for this file. Shrinking the secondary log to zero will return it to its original size (500 MB) and so "encourage" the active log to move swiftly back into the primary log file. It's important to note that this will not move any log records in the secondary log over to the primary log (some people expect this behavior because if we specify the
EMPTYFILE parameter, when shrinking a data file, SQL Server will move the data to another data file in the same filegroup).
As soon as a log backup means that the secondary log file contains no part of the active log, we can simply remove it.
USE MyMessages; GO ALTER DATABASE MyMessages REMOVE FILE MyMessages_Log2; GO
Shrink Primary Log and Remove Fragmentation
When we first created the
MyMessages database, we sized the primary log file at just 2 MB and allowed SQL Server to auto-grow it in 2MB increments to over 800 MB in size. A transaction log that auto-grows frequently, in small increments, will have a very large number of small Virtual Log Files (VLFs). This phenomenon is log fragmentation. If a database process, such as the crash recovery process, has to read the log file, it starts by reading in all the VLFs. If there are many of them, this will be a longer operation, and may affect the overall time taken to recover the database. A similar argument applies to other operations that read the log, such as log backups.
Essentially, the initial size and relatively small growth increments we've chosen for this database are inappropriate for this sort of data load and lead to the creation of a large number of VLFs. We can confirm this by interrogating the VLF architecture using a command called
LogInfo, as shown in Listing 8.
USE MyMessages DBCC Loginfo; -- returns 1672 rows
We're not going to discuss the output of
DBCC LogInfo in any detail. All we're interested in at this stage is that
DBCC LogInfo returns 1672 rows, meaning 1672 VLFs.
Interrogating VLFs using DBCC LogInfo
LogInfo is an undocumented and unsupported command. We'll use it in this whitepaper to peek at the VLFs, but we won't go into detail about the information it returns. Kalen Delany has a good blog post that explains its use, and output:
Note that when we created the 3 GB secondary log file, but this time setting a reasonable initial size (500 MB), followed by a manual growth, the secondary log file comprised only 24 new VLFs, a very reasonable number of virtual files.
The way to reduce the primary log file to a reasonable size, and remove the fragmentation, is to shrink it, and then manually resize it. Note that we should never shrink the log as part of our standard maintenance operations, as it will simply need to grow again as we add more data, and modify our existing data, and these log growth events are expensive, since SQL Server has to zero-initialize the new log space.
However, shrinking the log is permissible in situations such as this, in the knowledge that we have investigated and resolved the cause of the excessive log growth, and will then correctly size the log such that shrinking the log should be a "one off" event.
The recommended approach is use
DBCC SHRINKFILE (see http://msdn.microsoft.com/en-us/library/ms189493.aspx) to reclaim the space, and remove fragmentation. If we specify 0 (zero) as the target size, or don't specify a size, SQL Server will attempt to shrink the log back to its initial size. Alternatively, we can specify a target size to which to shrink the file (such as "1", if we wish SQL Server to order shrink the log to its smallest possible size). In Listing 11, we use zero in order to shrink the log back to its initial size (2 MB).
USE MyMessages; GO DBCC SHRINKFILE (N'MyMessages_log' , target_size=0); GO
In the output from this command, we see the current database size (91008*8-KB pages) and minimum possible size after shrinking (256*8-KB pages). This is actually an indication that the shrink did not work fully. SQL Server shrank the log to the point where the last VLF in the file contained part of the active log and then stopped. Check the messages tab for confirmation.
/*Cannot shrink log file 2 (MyMessages_log) because the logical log file located at the end of the file is in use. (1 row(s) affected) DBCC execution completed. If DBCC printed error messages, contact your system administrator.*/
Perform a log backup and try again.
BACKUP LOG MyMessages TO DISK = 'D:\SQLBackups\MyMessages_log3.trn' WITH INIT; USE MyMessages; GO DBCC SHRINKFILE (N'MyMessages_log' , 0); GO
Having done this, we can now manually grow the log to the required size, in a similar way to that demonstrated in Listing 6.
If a log file grows excessively, and you need to add a temporary secondary log file, remove it as soon as it is no longer required. If you leave the secondary log file in place, it may slow down considerably database restore and recovery operations.
It is a bad idea to undersize the transaction log, and then let SQL Server auto-grow it in an uncontrolled fashion, in small increments. This can lead to log fragmentation, which may slow down log backup and database recovery operations. The way to avoid issues relating to expensive log growth events, and log fragmentation, is simply to set the correct initial size for the log (and data) file, allowing for current requirements, and predicted growth over a set period.
Ideally, having done this, the log would never auto-grow, which isn't to say that we should disable the auto-growth facility. It must be there as a safety mechanism, and we should set a reasonable auto-growth increment in order to avoid growth events fragmenting the log. However, having sized the log appropriately, we are not relying on auto-growth being the mechanism that controls log growth.
If you’d like to learn more about how to build a "bullet-proof" backup and restore strategy for your databases, and optimize your transaction log management, check out the following resources:
Bulletproof your Database Backup and Recovery Strategy (free whitepaper)
SQL Server Backup and Restore, by Shawn McGehee (free eBook)
SQL Server Transaction Log Management, by Tony Davis and Gail Shaw (Paperback)