FIX: Slow performance when you recover a database if there are many VLFs inside the transaction log in SQL Server 2005, in SQL Server 2008 or in SQL Server 2008 R2

Consider the following scenario:

• You install Microsoft SQL Server 2005, Microsoft SQL Server 2008 or Microsoft SQL Server 2008 R2 on a computer.
• One or more of the databases are in the full or bulk-logged recovery model.
• The initial size of a transaction log file for the database is small. However, the transaction file grows large.
• The autogrow growth increment of the transaction log file is configured to a small size. For example, the autogrow growth increment is configured to one megabyte or to one percent. Or, you make the transaction log file increase by using a small manual growth.
• The transaction log file grows by the small growth increment. After a while, the transaction log file is large.
  
  For example, the transaction log file grows from one hundred megabytes to ten gigabytes. The transaction log file grows by one megabyte increment in each growth.

In this scenario, you may experience the following issues:

• Database recovery is slow when the database is started if there is a large volume of work to recover.
  
  For example, a transaction is pending open for a long time. Or, lots of transactions that are small in size are not committed when the SQL Server is shut down or restarted. In this case, you may receive an error for a long estimated recovery time in the analysis phase, in the undo phase, or in the redo phase. The error resembles the following and is logged in the SQL Server 2005 error log file:
  Analysis of database 'mydatabase' (7) is 0% complete (approximately 1234 seconds remain). This is an informational message only. No user action is required.
• Database recovery is slow when a full database backup file and an additional transaction log sequence are restored if the database is brought online.
  
  Note To bring the database online during the restore operation, use the WITH RECOVERY syntax or the WITH STANDBY = <options> syntax.
• Database mirroring recovery is slow on a partner server in the mirroring pair.
• The progress of the log reader agent for transactional replication is slow for the following operations:
  • The transaction log of a publisher database is parsed.
  • A command is added into a distribution database.
  • The transactions are replicated.
  
  Note For more information, visit the following Microsoft Developer Network (MSDN) website:
  949523

  (http://support.microsoft.com/kb/949523/ )

  The latency of a transactional replication is high in SQL Server 2005 when the value of the "Initial Size" property and the value of the Autogrowth property are small
• Slow performance occurs when a database snapshot is created if many transactions are logged or if the transaction logs stay active for a long time. This issue occurs because the snapshot has to run recovery and the transaction logs have to be rolled back.
• Slow performance occurs when you use the DBCC CHECKDB syntax to create a hidden database snapshot that has to fully recover the database snapshot to run the consistency checks.

These issues occur because the Virtual Log Files (VLFs) in a transaction log file might cause slow performance when SQL Server scans through these VLFs during the following operations:

• Database recovery
• Database mirroring
• Database snapshot
• Database transactional replication log reader activities

When a transaction log file for a database is grown by small increments during extreme growth conditions, thousands to hundreds of thousands of VLFs are present inside a single set of transaction log (.ldf) files. However, large log growth by large increments creates only several VLFs by comparison. For example, several might be less than 100 VLFs.

Note The VLFs are the divisions or segments that are used internally by SQL Server in the transaction log file.

Cumulative update information

SQL Server 2008 R2

The fix for this issue was first released in Cumulative Update 6. For more information about how to obtain this cumulative update package for SQL Server 2008 R2, click the following article number to view the article in the Microsoft Knowledge Base:Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 R2 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

SQL Server 2008 Service Pack 1

The fix for this issue was first released in Cumulative Update 12 for SQL Server 2008 Service Pack 1. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 fix release. Microsoft recommends that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base: Microsoft SQL Server 2008 hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2008 Service Pack 1 hotfix to an installation of SQL Server 2008 Service Pack 1. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.

SQL Server 2008 Service Pack 2

The fix for this issue was first released in Cumulative Update 2 for SQL Server 2008 Service Pack 2. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 fix release. Microsoft recommends that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base: Microsoft SQL Server 2008 hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2008 Service Pack 2 hotfix to an installation of SQL Server 2008 Service Pack 2. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.

SQL Server 2005 Service Pack 3

The fix for this issue was first released in Cumulative Update 13 for SQL Server 2005 Service Pack 3. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base: Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2005 fix release. Microsoft recommends that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base: Microsoft SQL Server 2005 hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2005 Service Pack 3 hotfix to an installation of SQL Server 2005 Service Pack 3. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.

SQL Server 2005 Service Pack 4

The fix for this issue was first released in Cumulative Update 1 for SQL Server 2005 Service Pack 4. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2005 fix release. Microsoft recommends that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base: Microsoft SQL Server 2005 hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2005 Service Pack 4 hotfix to an installation of SQL Server 2005 Service Pack 4. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

Install this hotfix and restart SQL Server might improve the recovery performance. For example, the performance might be improved for the following operations after you install this hotfix and restart SQL Server:

• Database recovery
• Database mirroring
• Database snapshot
• Database transactional replication log reader activities

If you cannot install this hotfix, you can perform the following workarounds to mitigate an existing issue and to prevent a future occurrence.

Mitigate an existing issue

• Wait for the restore or recovery operation to complete
  
  If you have a non-recovered database that is experiencing the slow performance when you restore or recovery the database, you may have to wait for the restore or recovery operation to be completed. For example, you might see the offline status or the recovering status in SQL Server Management Studio (SSMS) for a non-recovered database. Stopping SQL Server usually offers no relief for a slow recovery and may take more time to repeat the same recovery analysis phase, redo phase, or undo phase.
• Avoid restoring the transaction log sequence that contains thousands of VLFs
  
  If you experience the slow performance while you restore and recover a database by using a backup file, you can avoid restoring the transaction log sequences that contain thousands of VLFs. To identify the backup file that has the most the virtual log files recorded, use the following statement to see the FirstLSN and LastLSN columns in the log backup files:
  RESTORE HEADERONLY FROM DISK='C:\folder\file.trn'
  
  You can decide to avoid restoring the log backup files. Or, you can use the STOP AT statement in the RESTORE commands to avoid the highly fragmented parts of the transaction logs. If you do not fully restore the log sequences up to the latest point in time during a failure recovery scenario, data loss occurs in your database SQL Server. This data loss occurs because not all transactions are being kept. Therefore, there is a business tradeoff decision. You can fully restore a highly fragmented transaction log. However, this operation may take many hours. Or, you can use the STOP AT statement in the recovery to stop the recovery before the highly fragmented part of the log. However, any missing transactions that you omit are lost.
  
  Note Without installing this hotfix, there is typically no safe recourse for expedited recovery after you restart SQL Server. SQL Server has to locate the list of VLFs to analyze the log files, to redo completed transactions, and then to undo incomplete transactions to finish recovery to bring the database online safely. You cannot safely skip transactions during recovery.

Prevent a future occurrence

• Set the autogrow increment of the database to an appropriate size
  
  If the size of the autogrow increment is too small, there will be many Virtual Log Files (VLFs), and you may experience slow performance in SQL Server. If the size of the autogrow increment is too large, queries that make the transaction logs grow automatically may have to wait for a long time to finish a growth. Therefore, a time-out error may occur in SQL Server. To work around these issues, you can set the size of the autogrow increment for your database to an appropriate size.
• Eliminate the large number of VLFs, and use a manual growth
  
  If many VLFs are in the transaction log, reduce the size of the transaction log, and make it increase before peak business to meet the demand by using a manual growth. For example, the transaction log increases back to a reasonable average size in a large increment or in a single manual growth. Therefore, the size of the transaction log reaches a peak capacity, and the log backup files are scheduled on a frequent and periodic basis. Additionally, the transaction log might be truncated, and the VLFs for the transaction log can be reused in a cycle.
• How to shrink and increase the transaction log manually
  
  To correct a log that has too many VLFs, follow these steps to shrink the log and increase it again manually:
  1. If the database is in a full or bulk-logged recovery model, you have to back up the transaction log to allow for the active VLFs to be truncated and to be reused.

     BACKUP LOG databasename TO DISK='C:\folder\log_backupfile.trn'

     
     For more information about how to back up the transaction log file by using SSMS, visit the following Microsoft Developer Network (MSDN) website:
     How to back up the transaction log file by using SSMS

     (http://msdn.microsoft.com/en-us/library/ms179478.aspx)
     For more information about how to back up the transaction log file by using Transact-SQL statements, visit the following MSDN website:
     How to back up the transaction log file by using Transact-SQL statements

     (http://msdn.microsoft.com/en-us/library/ms191284.aspx)
  2. To determine the logical name of the transaction log file, run one of the following statements.
     Statement 1
     

     exec sp_helpfile 

     Statement 2
     

     select * from sys.sysfiles 

     To reduce the size of the transaction log file to the desired size, use the following code:

     DBCC SHRINKFILE(transactionloglogicalfilename, TRUNCATEONLY) 

  3. You can increase the size of the transaction log file to an appropriate size. We recommend that you let the size of the transaction log file grow to the peak regular size. Therefore, the autogrow increment is avoided. To set the size of the transaction log, use the Database Properties page in SSMS, or use the following ALTER DATABASE syntax:

     MODIFY FILE ( NAME = transactionloglogicalfilenae, SIZE=newtotalsize MB) 

     
     For more information about how to increase the size of a database in SSMS, visit the following MSDN website:
     How to increase the size of a database in SSMS

     (http://msdn.microsoft.com/en-us/library/ms175890.aspx)
     For more information about the ALTER DATABASE MODIFY FILE syntax, visit the following MSDN website:
     General information about ALTER DATABASE MODIFY FILE syntax

     (http://msdn.microsoft.com/en-us/library/bb522469.aspx)

You can check the number of VLF segments by reviewing the SQL error log file and then by finding the log sequence number (LSN) in each transaction log backup file. The first digits before the colon symbol in the LSNs correspond to the number of the LSN.

For example, the first number in the first informational message for the LSN is 1. However, the first number in the second informational message for the LSN is 100001. In this scenario, there are 100,000 VLFs that are used between the time of the first informational message and of the second informational message. Therefore, the logged fragmented transaction log that has many Virtual Log Files (VLFs) resembles the following:

For more information about the physical architecture of the transaction log, visit the following MSDN website: For more information about log sequence numbers (LSN), visit the following MSDN website: For more information about the 1413 error when database mirroring starts, visit the following MSDN website: For more information about how a log file structure can affect database recovery time, visit the following MSDN website: For more information about the transaction log VLFs, visit the following MSDN website: For more information about how to create a database snapshot, visit the following MSDN website:For more information about the Incremental Servicing Model for SQL Server, click the following article number to view the article in the Microsoft Knowledge Base: For more information about the naming schema for SQL Server updates, click the following article number to view the article in the Microsoft Knowledge Base: For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base: