This is a topic that’s been well covered, but in the interest of spreading good database practices, here’s a very quick note on instant file initialization (IFI) and database files. (As always, you should read the fine print – meaning Microsoft’s documentation – before you do anything on a production system.)
Instant File Initialization
IFI is something you must enable; it’s simple, and it makes file growths happen faster. MSDN says, “Instant file initialization is only available if the SQL Server (MSSQLSERVER) service account has been granted SE_MANAGE_VOLUME_NAME. Members of the Windows Administrator group have this right and can grant it to other users by adding them to the Perform Volume Maintenance Tasks security policy.” Here’s how you do that:
- Run SQL Server Configuration Manager on the server in question.
- Click on “SQL Server 2008 Services” (or whatever SQL version you have) and make note of the user account for SQL Server.
- Start > Run > SECPOL.MSC
- Expand “Local Policies”, click on “User Rights Assignment” and double-click “Perform volume maintenance tasks”.
- Click “Add User or Group” and add the user account.
- Click OK.
That’s it! See this MSDN article, this blog, and this blog for more details.
Growing a Database File
Once you get beyond smallish databases, it quickly becomes an even better idea to do planned data file growths.
Don’t use the GUI. Just don’t. I find it universally harder, more time consuming, and less flexible to administer databases via GUI. Learn some syntax.
In this case, what you want is ALTER DATABSE. First, find the logical file name of the file you want to grow. One of many ways to do this is with sp_helpdb MyDatabase.
sp_helpdb Adventureworks;
That’ll get you the file name AND the current file size. In my case, I have “AdventureWorks_Data” and “2648064 KB”, respectively. Now just customize your ALTER DATABSE statement with the DB name, file name, and the size you want the file to be, and run (after hours, please):
ALTER DATABASE Adventureworks MODIFY FILE (NAME = AdventureWorks_Data, SIZE = 5 GB);
Reference: MSDN’s ALTER DATABASE File and Filegroup Options page.
Fin.
-Jen McCown
http://www.MidnightDBA.com/Jen
