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.)
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:
That’s it! See this MSDN article, this blog, and this blog for more details.
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