A Quick Guide to Expanding Database Files and IFI

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:

  1. Run SQL Server Configuration Manager on the server in question.
  2. Click on “SQL Server 2008 Services” (or whatever SQL version you have) and make note of the user account for SQL Server.
  3. Start > Run > SECPOL.MSC
  4. Expand “Local Policies”, click on “User Rights Assignment” and double-click “Perform volume maintenance tasks”.
  5. Click “Add User or Group” and add the user account.
  6. 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

 

 

3 thoughts on “A Quick Guide to Expanding Database Files and IFI

  1. Justin Dearing

    That’s actually awesome to know there is a “go faster” button for database file growth. I’m a dev, but at some previous employers there is no real DBA so I’m the guy with sysadmin access. So I don’t always know these sorts of things.

    I”m also a former unix admin who worked under a very security conscious CTO with a black belt in Shotokan, which means when I setup SQL Server, I make a separate unprivileged user for it. So now I know to take these extra steps so autogrowth has its magic “gofaster” button.

    1. Jen McCown Post author

      Well excellent! Folk like you – devs – and junior DBAs (or, alternately, DBAs with gaps in their knowledge) are my target audience!

  2. Justin Dearing

    So a restore was being really slow this morning on a dev VM and google lead me to people talking about instant file initializations. Then I was all like “OMG Jen wrote that thing about that and I was like OMG thats awesome”

    So I added the permission, and restarted the server and things seem to be going faster.

Comments are closed.