16

If I want to create 5GB database with

CREATE DATABASE [test]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'test', FILENAME = N'E:\2012\test.mdf' , SIZE = 5529600KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'test_log', FILENAME = N'E:\2012\test_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

it takes 1 minute on my SSD.

But when I add SQL Server user to Perform volume maintenance tasks

enter image description here

it takes only 1-2 seconds.

Why is that? Can someone explain to me what are the reasons for this?

  • 4
    Can the downvoters please explain why the downvotes?? This looks like a fine question to me. – Thomas Stringer Feb 27 '15 at 16:21
25

That's because of Instant File Initialization. In short, SQL Server can take advantage of this privilege for database data files (not transaction log files). What this means is that SQL Server does not have to zero out the data file(s) when initializing.

Without the "Perform volume maintenance tasks" privilege granted to the SQL Service account, upon the database creation SQL Server needs to zero out "test.mdf" and "test_log.ldf".

With the "Perform volume maintenance tasks" privilege permission set, SQL Server only needs to zero out "test_log.ldf". A significantly less overhead, therefore minimized duration for the CREATE DATABASE in your testing.

References
How and Why to Enable Instant File Initialization
Instant Initialization – What, Why and How?

  • this seam that is feature is awesome, but why the default for it is off ? – ahmed abdelqader Apr 26 '17 at 17:46
1

This will not affect .ldf files (log files), at least not according to the MS Article

Why do it? Speed

Why NOT do it? Possible security risk. Someone could hack into your box and possibly read the non-zero'd memory spaces and retrieve that data. Small risk, but still a risk. This is why it is not enabled by default.

Detailed explanation: How and Why to Enable Instant File Initialization

  • Note to the reader: If you're concerned about people hacking into your server and reading old data from deleted file, leaving IFI off may be helpful, but it's hardly what I'd consider sufficient. And, if you've got software that overwrites deleted files already, then you'd probably be OK to turn IFI on, as that software should have already zeroed out the space. – RDFozz Nov 6 '17 at 15:51
  • There's now a checkbox during install (SQL 2016 onwards) to grant this right to the SQL Server service account so I guess MS are a little more relaxed on the security front too. – Gareth Lyons Nov 6 '17 at 15:55

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.