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

  • 2
    For creation of a large database you're far more limited by disk I/O than anything else. If IFI is enabled (as per the answers) then you can skip initialising (zeroing out) the data file, but no matter what you have to initialise the log file. If you're using a typical consumer spinny disk that could take up to half an hour for the log file alone. – Simon Righarts Oct 7 '13 at 20:19

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

  • what exactly happens on the hard disk when zero out all sectors and why SQL always zero out the log file? what is the advantage of doing that? – Mehrdad Kamelzadeh Oct 7 '13 at 20:41
  • This is the process of erasing data on your disk. Basically, instant file initialization skips that. However, because of how the log file works internally, SQL Server must zero it out completely in order to use it. You can find more detail on it here. – Mike Fal Oct 7 '13 at 20:47
  • @MehrdadKamelzadeh SQL server zeroing out file ONLY applies to Data files. Log files cannot be zero initialize. Refer to (sqlskills.com/blogs/paul/…). Also refer to How to tell if you have instant initialization enabled? (sqlskills.com/blogs/paul/…). – Kin Shah Oct 7 '13 at 20:48
  • 1
    @Kin - Log files must be zero initialized, but with IFI you can skip zero-initializing the data files. – Simon Righarts Oct 8 '13 at 4:23

If you run secpol.msc what is the value for Local Polices, User Rights Assignment, Perform Volume Maintenance Tasks?

My assumption at this point is that the SQL Server service account doesn't have this right and so it zeroing out the mdf and the LDF. Can't get around the LDF being zeroed out but you can skip it for the MDF.


Reference - http://timradney.com/2012/05/30/instant-file-initialization-for-sql-server-performance/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.