Monday, October 25, 2021

Backup Tune-up

SQL Saturday - Aug 4, 2012 - Baton Rouge

Instructor: Sean McCown (Midnight DBAs)

Backup Tune-Up

Contact Info:
DBAs@midnight - webshow

This post is going to be a copy of my notes which arent going to make a lot of sense, but when I review them at different times, I am hoping to pick up on maybe what he was saying when it is applicable. There is a lot of information on their website, including the demo that he performed

Restore >> Execution Plans >> log zeroing
- fill a full file with zeros
- then fills it in with SQL data

IFI - data files only

  • instant file initialization
  • turn on - local security policy >> user rights assignments >> 1) perform volume maintenance task and 2) restart SQL service

Trace Flags - change the normal behavoiur of SQL

  • 3014 - never gonna use - troubleshooting HEX info
  • 1806 - turns off IFI - current session
  • 3605 - send to SQL log
  • 3004 - zeroing data came from here
  • 3604 - send to client (not log)
  • 3213 - tells how many buffers using by default
  • 3212 - close to 3213 (sister to it)
  • There is a SQL Server Central post that displays a lot the known ones

Increase the speed of backups - best ways

  • compression - only works 2008 and above - enterprise and above
  • buffer count (# of files) - striping to separate drives (or same drive)

More things discussed/shown

  • buffers - memory to use
  • striping - threads to use
  • max transfer size - amount of data to write at a time - chunks of 64bytes (2MB is a bit excessive)
  • cant stop zeroing log files - growing log files (1 GB is ok)


  • read and write
  • NUL - goal - cant do any better - goal is to get as close to these numbers as possible
  • Find out how fast you can do it with all the resources available
  • Good to know you can do it BUT - if you do it you will hurt production resoruces
  • But - if things are stopped due to issues, its nice to know the smallest amount of time it will take if all resources are available


Powered by mod LCA