Certain database operations take a very long duration to complete or encounter errors when the transaction log has numerous virtual log files

Article translations Article translations
Article ID: 2028436 - View products that this article applies to.
Expand all | Collapse all

Symptoms

You might encounter the following problems:
  1. One or more databases take a very long time to finish the recovery during SQL Server startup
  2. When you perform restore of a database, it takes a very long time to complete
  3. Attempts to attach a database takes a very long time to finish
  4. When you attempt to setup database mirroring you encounter error messages 1413, 1443 and 1479 indicating a timeout.
  5. You encounter memory related errors like 701 when you attempt to restore a database

When you examine the SQL Server Error log, you notice that a significant amount of time is spent before the ANALYSIS phase of the database recovery process.
2010-05-08 14:42:38.65 spid22s Starting up database 'lot_of_vlfs'.
2010-05-08 14:46:04.76 spid22s Analysis of database 'lot_of_vlfs' (16) is 0% complete (approximately 0 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.

Cause

During the initial stages of a database recovery process, SQL Server proforms a discovery of all Virtual Log Files present in all the Transaction Log files. A list of all Virtual Log Files is built. This process can take a very long time depending upon upon the number of Virtual Log Files present in the specific database. A database can end up with a large number of Virtual Log Files if the Transaction Log encounters frequent auto-grow with the growth happening for very small size increments.

Normally you start encountering the problems mentioned in the symptoms section when the number of Virtual Log Files is in the range of several hundreds of thousands.

Resolution

You can use the methods from the following blog to find the number of Virtual Log Files in a particular Database:
How a log file structure can affect database recovery time
You might need to keep the total number of Virtual Log Files under a reasonable number like 10,000.

You can reconfigure the transaction log file to contain only a limited number of Virtual Log Files using the following methods:
  1. Shrink the Transaction log files, grow the files to the required sizes manually using the TSQL ALTER DATABASE <database name> MODIFY FILE (NAME='Logical file name of transaction log', SIZE = <required size>)
  2. Rebuild the transaction log file and then grow the transaction log files to required sizes manually
    1. If the database was previously shutdown clean [with no users or open transactions], you can make use of the FOR ATTACH_REBUILD_LOG option of CREATE DATABASE to create a new transaction log file.
    2. If a read/write database has a single log file that is currently unavailable, and if the database was shut down clean [with no users or open transactions] before the attach operation, FOR ATTACH option of CREATE DATABASE automatically rebuilds the log file and updates the primary file.

After you reconfigure the layout of the Transaction log file, review and make necessary changes to the auto-grow settings for the transaction log file to avoid encountering the same problem in the future.

Note Before you perform any of these operations, make sure that you have a valid restorable backup in case you encounter some issues later.

2524743 FIX: Recovery takes longer than expected for a database in a SQL Server 2008 or in a SQL Server 2008 R2 environment

2455009 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

2653893 FIX: It takes a long time to restore a database in SQL Server 2008 R2

979042 FIX: The principal database is not recovered if the database has a large number of virtual log files in SQL Server 2005 or in SQL Server 2008

More information

For complete information on Virtual Log files, refer to the Books Online topic: Transaction Log Physical Architecture
Discussions on this problem:
How a log file structure can affect database recovery time
Transaction Log VLFs - too many or too few?
1413 error when starting Database Mirroring – How many virtual log files is too many?

Also take note of a known issue with growing transaction logs:

2633151 The SQL Server database transaction log file does not grow by the configured file growth value


For more information about the products or tools that automatically check for this condition on your instance of SQL Server and on the versions of the SQL Server product, see the following table:

Collapse this tableExpand this table
Rule softwareRule titleRule descriptionProduct versions against which the rule is evaluated
SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA)




Databases with high number of VLF present





The SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA) provides a rule to detect situations where the databases contain a large number of Virtual Log Files. The SQL Server 2008 R2 BPA supports both SQL Server 2008 and SQL Server 2008 R2.

If you run the BPA tool and encounter an Error with the title of Database Engine - Databases with high number of VLF present, then you need to verify the number of Virtual Log Files for the affected database and reconfigure the transaction log files.
SQL Server 2008
SQL Server 2008 R2






SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA)



Databases with high number of VLF present




The SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA) provides a rule to detect situations where the databases contain a large number of Virtual Log Files.

If you run the BPA tool and encounter an Error with the title of Database Engine - Databases with high number of VLF present, then you need to verify the number of Virtual Log Files for the affected database and reconfigure the transaction log files.
SQL Server 2012








Properties

Article ID: 2028436 - Last Review: May 7, 2014 - Revision: 9.0
Applies to
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 R2 Datacenter
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Express
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Express
  • Microsoft SQL Server 2008 R2 Web
  • Microsoft SQL Server 2008 R2 Workgroup
  • Microsoft SQL Server 2008 Web
  • Microsoft SQL Server 2012 Developer
  • Microsoft SQL Server 2012 Enterprise
  • Microsoft SQL Server 2012 Express
  • Microsoft SQL Server 2012 Standard
  • Microsoft SQL Server 2012 Web
  • Microsoft SQL Server 2014 Developer
  • Microsoft SQL Server 2014 Enterprise
  • Microsoft SQL Server 2014 Express
  • Microsoft SQL Server 2014 Standard
  • Microsoft SQL Server 2014 Web
Keywords: 
KB2028436

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com