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
-
2For 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– Simon Righarts2013年10月07日 20:19:50 +00:00Commented Oct 7, 2013 at 20:19
2 Answers 2
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 sectorsand why SQL always zero out the log file? what is the advantage of doing that?Mehrdad Kamelzadeh– Mehrdad Kamelzadeh2013年10月07日 20:41:04 +00:00Commented Oct 7, 2013 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– Mike Fal2013年10月07日 20:47:34 +00:00Commented Oct 7, 2013 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– Kin Shah2013年10月07日 20:48:41 +00:00Commented Oct 7, 2013 at 20:48
-
1@Kin - Log files must be zero initialized, but with IFI you can skip zero-initializing the data files.Simon Righarts– Simon Righarts2013年10月08日 04:23:24 +00:00Commented Oct 8, 2013 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.
SecPol
Reference - http://timradney.com/2012/05/30/instant-file-initialization-for-sql-server-performance/