Home » Beginner »sql server »sqlserverpedia-syndication »SSC »TSQL » Currently Reading:

A Quick Guide to Expanding Database Files and IFI

July 26, 2012 Beginner, sql server, sqlserverpedia-syndication, SSC, TSQL 2 Comments

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

 

 

Currently there are "2 comments" on this Article:

  1. 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.

Comment on this Article:







MidnightSQL Consulting

Need help? Got an emergency? Write us at Support@MidnightDBA.com!

We can schedule time to help with your backup/restore issues, high availability and disaster recovery setup, performance problems, and a great deal more. Very often, we're even available on the moment for downtime issues and emergencies.

For more information about MidnightSQL consulting, email us or check out www.MidnightSQL.com. Happy days!

Where are We?

September 13: SQL Saturday Kansas City
November 3-7: PASS Summit, Seattle, WADecember 11: Presenting "Powershell Cmdlets.." at Alaska SQL Server User Group

Blog Posts by Category

DBAs@Midnight

How to Eat Pop-tarts
Watch DBAs@Midnight live on Fridays,m 11pm Central time

The best database career advice you’ve never heard!

DBARoadmap.com

The DBA Roadmap Seminar is 7 MP3 tracks (over 5 hours!) of insider guidance on your database career. We'll teach you how and what to study as a DBA, weigh in on controversial resume debates, teach you to recognize a worthy recruiter, and discuss the new professionalism of interviews. Also some bonus materials, PDF companion guides, and really spiffy intro music!

Once your $99 PayPal payment is submitted, you'll get the download link in e-mail! (Download is a 370Mb ZIP file.)

Become a DBA. Become a BETTER DBA. Use the Roadmap.

Visit www.DBARoadmap.com for info, forums, and more!

Add to Cart View Cart

Cunningham’s Law

"The best way to get the right answer on the Internet is not to ask a question, it's to post the wrong answer."
Relevant: http://xkcd.com/386/