HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlModerate

Create new database with large initial size

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
newcreatewithsizedatabaselargeinitial

Problem

I want to create a new database with a 200GB data file (.mdf) and 50GB log file (.ldf).

But it is very slow (it's about 15 minutes and still hasn't created). and it is very time consuming. Is that normal? if yes what does it do that takes time? can I enhance its creating speed?

I am using SQL Server 2008 R2, Windows Server 2008R2, 16GB RAM (which I limited that to 12GB in ssms) and Core i7 Cpu

Solution

Unless you're using Instant File Initialization, SQL Server must zero out all sectors for the files. This is a very time consuming process, especially for 250 GB (data+log). In order to speed things up, enable this feature and then SQL Server will not zero out the space for the data files.

Please note, SQL Server will always zero out the log file, so you will still be limited by that. Another reference for log file initialization

Context

StackExchange Database Administrators Q#51155, answer score: 17

Revisions (0)

No revisions yet.