A fragmented log file can dramatically slow down any operation that needs to read the log file. For example, it can cause slow startup times (since SQL Server reads the log during the database recovery process), slow RESTORE operations, and more. Log size and growth should be planned and managed to avoid excessive numbers of growth events, which can lead to this fragmentation.
A log is fragmented if it contains a very high number of VLFs. In general, SQL Server decides the optimum size and number of VLFs to allocate. However, a transaction log that auto-grows frequently in small increments, will suffer from log fragmentation. To see this in action, let's simply re-create our previous ForceFailure database, withal its configu-ration settings set at whatever the model database dictates, and then run the DBCC LogInfo command, which is an undocumented and unsupported command (at least there is very little written about it by Microsoft) but which will allow us to interrogate the VLF architecture.
Chapter 5: Log Backups
CREATE DATABASE [ForceFailure]
ALTER DATABASE [ForceFailure] SET RECOVERY FULL WITH NO_WAIT GO
DBCC Loginfo;
GO
Listing 5-10: Running DBCC Loginfo on the ForceFailure database.
The results are shown in Figure 5-13. The DBCC LogInfo command returns one row per VLF and, among other things, indicates the Status of that VLF. A Status value of 2 indicates a VLF is active and cannot be truncated; a Status value of 0 indicates an inactive VLF.
Figure 5-13: Five VLFs for our empty ForceFailure database.
Five rows are returned, indicating five VLFs (two of which are currently active). We are not going to delve any deeper here into the meaning of any of the other columns returned.
Now let's insert a large number of rows (one million) into a VLFTest table, in the ForceFailure database, using a script reproduced by kind permission of Jeff Moden (www.sqlservercentral.com/Authors/Articles/Jeff_Moden/80567/), and then rerun the DBCC LogInfo command, as shown in Listing 5-11.
USE ForceFailure ;
GO IF OBJECT_ID('dbo.VLFTest', 'U') IS NOT NULL DROP TABLE dbo.VLFTest ;
--===== AUTHOR: Jeff Moden
--===== Create and populate 1,000,000 row test table.
-- "SomeID" has range of 1 to 1000000 unique numbers -- "SomeInt" has range of 1 to 50000 non-unique numbers -- "SomeLetters2";"AA"-"ZZ" non-unique 2-char strings -- "SomeMoney"; 0.0000 to 99.9999 non-unique numbers -- "SomeDate" ; >=01/01/2000 and <01/01/2010 non-unique -- "SomeHex12"; 12 random hex characters (ie, 0-9,A-F) SELECT TOP 1000000
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2 ; DBCC Loginfo;
GO
--Tidy up once you're done as we no longer need this database DROP DATABASE ForceFailure
GO
Listing 5-11: Inserting one million rows and interrogating DBCC Loginfo.
This time, the DBCC LogInfo command returns 131 rows, indicating 131 VLFs, as shown in Figure 5-14.
Chapter 5: Log Backups
Figure 5-14: 131 VLFs for our ForceFailure database, with one million rows.
The growth properties inherited from the model database dictate a small initial size for the log files, then growth in relatively small increments. These properties are inappro-priate for a database subject to this sort of activity and lead to the creation of a large number of VLFs. By comparison, try re-creating ForceFailure, but this time with some sensible initial size and growth settings (such as those shown in Listing 5-1). In my test, this resulted in an initial 4 VLFs, expanding to 8 VLFs after inserting a million rows.
The "right" number of VLFs for a given database depends on a number of factors, including, of course, the size of the database. Clearly, it is not appropriate to start with a very small log and grow in small increments, as this leads to fragmentation. However, it might also cause problems to go to the opposite end of the scale and start with a huge (tens of GB) log, as then SQL Server would create very few VLFs, and this could affect log space reuse. Further advice on how to achieve a reasonable number of VLFs can be found in Paul Randal's TechNet article Top Tips for Effective Database Maintenance, at
http://technet.microsoft.com/en-us/magazine/2008.08.database.aspx.
If you do diagnose a very fragmented log, you can remedy the situation using DBCC SHRINKFILE, as described in the previous section. Again, never use this as a general means of controlling log size; instead, ensure that your log is sized, and
Summary
This chapter explained in detail how to capture log backups using either SSMS Backup Wizard or T-SQL scripts. We also explored, and discussed, how to avoid certain
log-related issues such as explosive log growth and log fragmentation.
Do not to remove any of the backup files we have captured; we are going to use each of these in the next chapter to perform various types of restore operation on our DatabaseForLogBackups database.
Chapter 6: Log Restores
Whereas log backups will form a routine part of our daily maintenance tasks on a given database, log restores, at least in response to an emergency, will hopefully be a much rarer occurrence. However, whenever they need to be performed, it's absolutely vital the job is done properly.
In this chapter, we're going to restore the full backup of our DatabaseForLogBackups database from Chapter 5, and then apply our series of log backups in order to return our databases to various previous states. We'll demonstrate how to perform a complete trans-action log restore, and how to restore to a point in time within a log file.