SQL Indexing and VEAM/VMWare Backups Speed Up

DH_2D00_073113_2D00_1[1]Recently we were having issues with the time period of our Veam backups of SQL database. Veam creates a snapshot of a server, which can then be copied to disk or to tape subsiquently. The SQL servers were having their Indexes fully rebuilt every night.

“If you’re using the FULL recovery model, the entire index rebuild operation is fully logged, which means the transaction log file must be at least as large as the index being rebuilt. It also means the next
transaction log backup will essentially contain the entire index.” ( Per http://sqlmag.com/blog/it-bad-idea-rebuild-all-indexes-every-night  )

This balloned the storage needed on the SAN nightly by more than 1TB due to the writes of the transaction log and the changes from the inital snapshot and also slowed down the overall backup process

A smarter way to index the servers each night is to analyse the fragmented indexes , and only reindex thoose

This can be found here : http://blogs.technet.com/b/sql_server_isv/archive/2010/10/18/index-fragmentation-if-it-isn-t-broke-don-t-fix-it.aspx

Indexing tasks and scipts can be found here : http://technet.microsoft.com/en-us/library/ms189858.aspx

 

 

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...