instant file initialization

• Indianrock

  SSC-Insane

  Points: 20333

  More actions

  July 1, 2009 at 8:14 am

  #1018431

  No sensitive data in our case. I'd like to test IFI on a dev sql server currently getting a lot of data activity. It's running sql under the local system account. I believe this account is actually called "SYSTEM" and that's what I would add to the "Perform volume maintenance tasks" user right. ??

• alen teplitsky

  SSC-Dedicated

  Points: 30014

  More actions

  July 1, 2009 at 1:07 pm

  #1018663

  GilaMonster (7/1/2009)

  ---

  SQL Noob (6/30/2009)

  ---

  only time i have this problem is when restoring a database through netbackup. a lot of times the job will fail while the files are initializing. i just restart it and it works. no big deal and probably won't bother turning this on

  What's the biggest DB that you work with?

  2TB or so. we have a bunch of databases ranging from a few MB to 2TB. first encountered this around 2 years ago when one of critical db's hit the 100GB or so mark. i had to restore to QA and it took a while to figure out. Still see this on new hardware sometimes, most recently with a new server with 32GB of RAM and 10TB of storage using HP SATA drives. I even set the RAID controller to 75% write and still happens

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  July 1, 2009 at 1:14 pm

  #1018667

  Writing 2TB of zeros will take a while. That's exactly what instant file initialisation was added to prevent - the need to first write over the data file with zeros and then to go and write all the restored data.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  July 1, 2009 at 1:28 pm

  #1018672

  +1 to Gail's comment about zeroing out large files.

  Also, you stated 10TB of SATA drives - that is going to suck for performance. SATA drives are not nearly as fast as the best SCSI/SAS drives. Heck, you can get 10TB of storage on fewer than 10 spindles these days. Sure hope you have a LOT more drives than that!! :w00t:

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service