In this final section, we'll consider appropriate action in the face of a bloated and fragmented log file. Perhaps a database only recently fell under our care; we've imple-mented some monitoring and realized that the log is almost full and that there isn't the capacity on its disk drive to accommodate an urgent index maintenance operation. We try a log backup but, for reasons we need to investigate further (see Chapter 7), SQL Server will not truncate the log. In order to buy some time, we add a secondary log file, on a separate disk, and the operation proceeds as planned.
We investigate why the log ballooned in size and it turns out to be an application leaving
"orphaned transactions" in the database. The issue is fixed, and the next log backup truncates the log, creating plenty of reusable space.
The next question is "what next?" given that we now have a database with multiple log files and a principal log file that is bloated and likely to be highly fragmented.
The first point is that we want to get rid of that secondary log file as soon as possible. As noted previously, there is no performance advantage to having multiple log files and, now that it's no longer required, all it will really do is slow down any restore operations, since SQL Server has to zero-initialize the log during full and differential restore operations.
Run Listing 8.4 to re-create the PersonsLots database, followed by Listings 8.2 and 8.3 to create and populate the Persons table (we provide the complete script, PersonsLots_2logs.sql, in the code download).
Let's assume, at this point, the DBA adds a second 3-GB log file to accommodate database maintenance operations.
USE master
GO ALTER DATABASE PersonsLots
ADD LOG FILE ( NAME = N'PersonsLots_Log2',
FILENAME = N'D:\SQLData\Persons_lots2.ldf' , SIZE = 3146000KB , FILEGROWTH = 314600KB )
GO
Listing 8.19: Adding a 3-GB secondary log file to PersonsLots.
Some time later, we've fixed the problem that resulted in delayed log truncation; there is now plenty of reusable space in the primary log file, and we no longer need this secondary log file, but it still exists. Let's restore the PersonsLots database.
USE master
GO RESTORE DATABASE PersonsLots
FROM DISK ='D:\SQLBackups\PersonsLots_full.bak' WITH NORECOVERY;
RESTORE DATABASE PersonsLots
FROM DISK='D:\SQLBackups\PersonsLots.trn' WITH Recovery;
/*<output truncated>…
Processed 18094 pages for database 'PersonsLots', file 'PersonsLots_log' on file 1.
Processed 0 pages for database 'PersonsLots', file 'PersonsLots_Log2' on file 1.
RESTORE LOG successfully processed 18094 pages in 62.141 seconds (2.274 MB/sec).*/
Listing 8.20: Restoring PersonsLots (with secondary log file).
The restore took over 60 seconds. If we repeat the exact same steps, but without adding the secondary log file, the comparative restore, in our tests, took about 8 seconds.
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 to zero (demonstrated shortly), and turn off auto-growth for this file, as this will
"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.)
Once the secondary log file contains no part of the active log, we can simply remove it.
USE PersonsLots
GO ALTER DATABASE PersonsLots REMOVE FILE PersonsLots_Log2 GO
Listing 8.21: Removing the secondary log file.
This is one problem solved, but we may still have a bloated and fragmented primary log.
While we should never shrink the log as part of our standard maintenance operations, as discussed in Chapter 7, it is permissible in situations such as this, in the knowledge that we have investigated and resolved the cause of the excessive log growth, and so shrinking the log should be a "one-off" event.
The recommended approach is to use DBCC SHRINKFILE (see http://msdn.microsoft.
com/en-us/library/ms189493.aspx) to reclaim the space. If we don't specify a target size, or if we specify 0 (zero) as the target size, we can shrink the log back to its original size (in this case, 2 MB) and minimize fragmentation of the log file. If the initial size of the log was large, we wish to shrink the log smaller than this, in which case we specify a target_size, such as "1".
USE PersonsLots
GO DBCC SHRINKFILE (N'PersonsLots_log' , target_size=0) GO
Listing 8.22: Shrinking the primary log file (partial success).
In the output from this command, we see the current database size (24128*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 (PersonsLots_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.
USE master
GO BACKUP DATABASE PersonsLots
TO DISK ='D:\SQLBackups\PersonsLots_full.bak' WITH INIT;
GO
BACKUP LOG PersonsLots
TO DISK = 'D:\SQLBackups\PersonsLots.trn' WITH init
USE PersonsLots
GO DBCC SHRINKFILE (N'PersonsLots_log' , 0) GO
Listing 8.23: Shrinking the primary log file after log backup.
Having done this, we can now manually grow the log to the required size, as demonstrated previously in Listing 8.8.
Summary
We started with a brief overview of the physical architecture factors that can affect log throughput, such as the need to separate log file I/O onto a dedicated array, and choose the optimal RAID level for this array.
This chapter then emphasized the need to manage transaction log growth explicitly, rather than let SQL Server auto-growth events "manage" it for us. If we undersize the log initially, and then let SQL Server auto-grow it in small increments, we'll end up with a fragmented log. Examples in the chapter demonstrated how this might affect the performance of any SQL Server operations that need to read the log.
Finally, we discussed the factors that determine the correct log size, and correct auto-growth increment for a given database, and we offered advice on how to recover from a situation where a database suffers from multiple log files and an oversized and fragmented primary log.
The next chapter, the final one in this book, will describe the various tools and techniques for monitoring log activity, throughput, and fragmentation.