How long does it take to create a database?

How long does it take to create a database? 

Well, depends on the piece of string on your pocket, of course – how big is the database, how zippy your server is, and so on. 

But ideally, you want to create it ASAP. 

Does the database ever need to grow? 

Well, if you have sized it perfectly, it will never need to grow.  <Howls of derisive laughter>

OK, seriously, of course it does, and you want it to grow as quickly as possible too. 

By default, before it creates or grows a file, SQL Server pauses for a moment, maybe quite a few moments, to wipe out any existing data by writing zeroes all over the space it needs.  Which is a sensible precaution if you want to make absolutely sure that nobody can hack into your server and read stuff that you had previously deleted.  But if you are writing data over it anyway, that might be a bit excessively paranoid.  Or maybe you ARE excessively paranoid?  Perhaps you have enough security in place that you would like to consider trading that precaution for speed and performance.  Only you can decide that.  

Find a development server, and check the file location of an existing database, like this:

use dbadmin

go

sp_helpfile

Now create a database.  See how long it takes.  In my Dev environment, this ran in 3 minutes, 17 seconds:

USE [master]

GO

CREATE DATABASE [Fred] ON  PRIMARY

( NAME = N'Fred_Data', FILENAME = N'G:\data\Fred_Data.mdf' , SIZE = 102596608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0)

LOG ON

( NAME = N'Fred_Log', FILENAME = N'H:\logs\Fred_Log.ldf' , SIZE = 2621440KB , MAXSIZE = 2048GB , FILEGROWTH = 0)

GO

Okay,  three minutes for 100GB, not totally shabby. 

Let’s try it without writing the zeroes.  It's called Instant File Initialization, if you want to do any further reading - I like Brent Ozar's write-up

It isn’t a box to tick or a script to run, I’m afraid.   you need to restart the SQL Server service, so it will have to be done out of hours on Production.

First thing you need is the name of your service account:

-- 2008r2 onwards

SELECT service_account, * FROM sys.dm_server_services;

For example - e.g. GANDALF\sqldev_frodo

Open a Remote Desktop Connection to the server

Run SECPOL.MSC (Local Security Policy)

Open up the Local Policies

Click on User Rights Assignment

Scroll right down to Perform Volume Maintenance Tasks

Open it up and add the service account

Click on OK

Now you need to restart the SQL Server Service

Run SERVICES.MSC

Scroll down to the SQL Server services – there are probably a few of them

Stop the SQL Server Agent (MSSQLSERVER) service first

Then stop SQL Server (MSSQLSERVER)

Then start SQL Server (MSSQLSERVER)

And finally start SQL Server Agent (MSSQLSERVER)

Why in that order?  If you stop SQL Server (MSSQLSERVER), the Agent service should stop automatically

If you restart SQL Server (MSSQLSERVER), it should stop, stop the agent, and restart (though you have to remember to restart the agent).

So why bother with the extra steps?

Because that is what SHOULD happen.  Sometimes the restart thing gets stuck.  This may be me remembering something that used to happen and has now been fixed, senile old fool, stuck in his ways.  So feel free to try restart.  Just don’t come whingeing to me if it doesn’t do what it should do.  Because I shall say I Told You So

Back to that script.  Remember the script to create the database?

USE [master]

GO

DROP DATABASE [Fred]

GO

CREATE DATABASE [Fred] ON  PRIMARY

( NAME = N'Fred_Data', FILENAME = N'G:\data\Fred_Data.mdf' , SIZE = 102596608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0)

LOG ON

( NAME = N'Fred_Log', FILENAME = N'H:\logs\Fred_Log.ldf' , SIZE = 2621440KB , MAXSIZE = 2048GB , FILEGROWTH = 0)

GO

Before, this ran in 3 minutes, 17 seconds.

Now?  18 seconds. 

That’s a pretty good improvement