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
Indexing tasks and scipts can be found here : http://technet.microsoft.com/en-us/library/ms189858.aspx
Trackback from your site.