SQL SERVER – Troubleshooting: When Database Creation Takes Long Time

This might not be an issue which would be faced by SQL DBAs regularly because databases are generally already created. There might be few DBAs out there who take care of deployment as well. So, if you are having an issue with slow database creation, then you have found the right blog to try some troubleshooting.

Are you running command without size?

If yes, then you should know what is the size SQL Server would assume. Going back to basics, the model database in SQL Server is used as the template for all databases created on an instance of SQL Server. The entire contents of the model database, including database options, are copied to the new database.  So, what are the things you would check?

Yes, we would check the size of the model database. I remember a client who changed the model database default size for database and log file and we found it be set more than 1024MB each in our environment. Earlier it was taking 10 minutes to create the new database (we were just running CREATE DATABASE FOO) and as soon as When we reduced the initial size of both files in the model database to ideal values, we could create a new database within 8 to 10 seconds.

I always rely on DMVs to give me some pointers about what’s going on. Below is one of my favorite commands

SELECT * FROM sys.dm_exec_requests WHERE SESSION_ID=[Yoursessionid]

When I checked the last_wait_type for the database creation SPID, it was found to be IO_COMPLETION.

Are you running command with size?

If you are running something like below

CREATE DATABASE [MyHUGEDatabase] ON PRIMARY (
	NAME = N'MyHUGEDatabase_dat'
	,FILENAME = N'D:HugeDBMyHUGEDatabase.mdf'
	,SIZE = 150000 MB
	,MAXSIZE = 400000 MB
	,FILEGROWTH = 51200 KB
	) LOG ON (
	NAME = N'MyHUGEDatabase_log'
	,FILENAME = N'D:HugeDBMyHUGEDatabase_log.ldf'
	,SIZE = 50000 MB
	,MAXSIZE = 100000 MB
	,FILEGROWTH = 15360 KB
	) COLLATE SQL_Latin1_General_CP1_CI_AS
GO

Watch out for the size given in the above query. 150 GB data file and 100 GB Log file. It is debatable if we need 100 GB of transaction log file for a 150 GB database. But keeping that point aside, we need to check whether we have the “” set for the SQL Server service account? If not, then enabling it will improve the initialization of the data files when creating/expanding them. This was also explained in many blogs. This is called as Instant File Initialization. You can search for that and get tons of article and samples.

Did you find this useful?

Reference: Pinal Dave (http://blog.SQLAuthority.com)