File initialization takes a long time for SQL Server database-related operations


Symptoms


File operations may take a long time on the SQL Server. For example, it may take a long time to perform any of the following operations:
  • Create a database
  • Grow a file
  • Restore a database
Additionally, it may take longer to create a tempdb database during server startup.

Additionally, error 5144 or error 5145 may be logged in the Application log. This occurs if the database files grow automatically because of the autogrow settings, and the operation takes a long time.

Cause


The database file operations that are mentioned in the "Symptoms" section require a long file initialization process. During this process, the file (that is, new regions of the file) are filled with zeros. This can take a long time, depending on the response time and capacity of the storage system.

SQL Server can skip filling in zeros during file initialization under the following conditions:
  • The SQL Server service startup account has the SE_MANAGE_VOLUME_NAME user right. (Grant the Perform Volume Maintenance Tasks right to the service startup account.)
  • The database file that is being initialized is a data file.

This feature is called Instant File Initialization. This feature is not applicable when the following conditions are true:
  • The database file is a transaction log file.
  • Transparent Data Encryption is enabled on the database.
  • The Windows operating system or file system does not support a call to Windows API SetFileValidData().
  • The database is a snapshot of another database.
  • The Instant File Initialization feature is explicitly disabled by using trace flag 1806.

Note This article makes no explicit claims of supportability for trace flag 1806. It is referenced here only because it is one way to disable Instant File Initialization.

Resolution


Important Carefully review the security implications of Database File Initialization in the MSDNdocumentation.

To resolve this problem, grant the SE_MANAGE_VOLUME_NAME user right to the SQL Server service startup account.

More Information


The following table provides more information on the products or tools that automatically check for this condition on your instance of SQL Server and the versions of the SQL Server product that the rule is evaluated against.
Rule softwareRule titleRule descriptionProduct versions against which the rule is evaluated
System Center AdvisorThe SQL Server "Instant File Initialization" feature is not enabledSystem Center Advisor determines whether the SQL Server startup account is granted the SE_MANAGE_VOLUME_NAME user right. This user right can be granted directly to the service startup account or through a group membership. System Center Advisor checks for only one level of group membership for the effective user right. If the service startup account does not have this user right, System Center Advisor generates a warning for this instance of SQL Server. System Center Advisor does not generate the alert if Instant File Initialization is disabled explicitly through the trace flag that is described earlier.SQL Server 2008
SQL Server 2008 R2


For information about the topics that are discussed in this article, click the following links:

How and Why to Enable Instant File Initialization
http://blogs.msdn.com/b/sql_pfe_blog/archive/2009/12/23/how-and-why-to-enable-instant-file-initialization.aspx


Myth #3:Instant file initialization can be a) enabled and b) disabled from within SQL Server
http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(330)-instant-file-initialization-can-be-controlled-from-within-SQL-Server.aspx

2091024 SQL Server reports Msg 5144 and 5145 for long or failed autogrow of databases and transaction log files