Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. Database operations take a long time to complete, or they trigger errors when the transaction log has numerous virtual log files

Database operations take a long time to complete, or they trigger errors when the transaction log has numerous virtual log files

View products that this article applies to.

Symptoms

In a SQL Server 2008 environment, you may encounter the following symptoms:
  • One or more databases take a very long time to finish recovery during SQL Server startup.
  • When you perform restore of a database, it takes a very long time to complete.
  • Attempts to attach a database take a very long time to finish.
  • When you try to set up database mirroring, you encounter error messages 1413, 1443 and 1479, indicating a timeout.
  • 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.

↑ Back to the top

Cause

During the initial stages of a database recovery process, SQL Server performs 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 on 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 hundred thousand.

↑ Back to the top

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 have to keep the total number of Virtual Log Files under a reasonable number, such as 10,000.

You can reconfigure the transaction log file to contain only a limited number of Virtual Log files by 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 manually grow the transaction log files to the required sizes:
    1. If the database was previously shut down cleanly (with no users or open transactions), you can use 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

2882905 A 9017 informational message is logged when you start an instance of SQL Server or restore or attach a database

↑ Back to the top

More Information

For complete information about virtual log files, see the following Books Online topic: Transaction Log Physical Architecture

For more information about this problem, see the following:

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 be aware 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




Note Learn about an important change to the VLF creation algorithm in SQL Server 2014:

http://www.sqlskills.com/blogs/paul/important-change-vlf-creation-algorithm-sql-server-2014

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:

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








↑ Back to the top

Keywords: kb

↑ Back to the top

Article Info
Article ID : 2028436
Revision : 1
Created on : 1/7/2017
Published on : 8/5/2015
Exists online : False
Views : 556