Why does allocating large databases take so long by default with Microsoft SQL?
Wednesday, May 25th, 2011 by Andrew Kuhlmann (See all posts by Andrew Kuhlmann)
A common problem which occurs when going to allocate a new database in Microsoft SQL is an extremely long operation which will occur by default due to a disk zeroing operation that is performed when creating a new database. By default this will take a very long time even on the most powerful of database servers.
The reason behind this is fairly simple to understand. Rather than deleting a file, SQL actually goes out and zeroes the space on the drive. Obviously zeroing the space on the drive is a lot more intensive of an operation than simply flagging it as deleted and allowing the disk controller to simply overwrite that sector next time it needs some space.
Of course there’s a very minor security risk here in that if someone were able to take total control of this disk they could theoretically use a disk recovery utility to read any data that was previously existing. Otherwise, there is really no disadvantage to simply erasing the space.
In order to allow SQL Server to allocate database quickly using Instant File Initialization, a security policy change needs to be made. This change can be made on either a computer level or a domain level. In most circumstances this would likely be made on a domain level in order to reduce administration overhead.
To enable Instant File Initialization perform the following steps:
1. Run secpol.msc on a SQL server. Expand Local Policies, click User Rights Assignment. On the right side, right click/properties “Perform Volume Maintenance Tasks.”
From there, you’ll see the following menu. Simply add the service account for your SQL database engine. You can also add the instance itself by its local object which will be something like: SQLServerMSSQLUser$ (computer name) $MSSQLSERVER
This may change based on if your SQL instance is named, and can be confirmed by using the secpol.msc snap-in to determine the actual name of the local account.
Also note that when you go to browse/add this in the properties menu for the actual security policy that you’ll need to select your computer name instead of the domain name.
Again, assigning this permission by the computer or by the domain service account accomplishes the same thing and is up to the administrator to determine based on their organizational security policy.
