{"id":1067,"date":"2013-08-20T00:17:30","date_gmt":"2013-08-20T00:17:30","guid":{"rendered":"http:\/\/pariswells.com\/blog\/?p=1067"},"modified":"2013-08-20T00:17:30","modified_gmt":"2013-08-20T00:17:30","slug":"sql-indexing-and-veamvmware-backups-speed-up","status":"publish","type":"post","link":"https:\/\/pariswells.com\/blog\/random\/sql-indexing-and-veamvmware-backups-speed-up","title":{"rendered":"SQL Indexing and VEAM\/VMWare Backups Speed Up"},"content":{"rendered":"\r\n

\"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\u00a0Indexes fully rebuilt every night.<\/p>\r\n

“If you\u2019re 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<\/a>\u00a0 )<\/p>\r\n

This\u00a0balloned 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<\/p>\r\n

A smarter way to index the servers each night is to analyse the fragmented indexes , and only reindex thoose<\/p>\r\n

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<\/a><\/p>\r\n

Indexing tasks and scipts can be found here : http:\/\/technet.microsoft.com\/en-us\/library\/ms189858.aspx<\/a><\/p>\r\n

 <\/p>\r\n

 <\/p>\r\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[595,592,591,593,594],"class_list":["post-1067","post","type-post","status-publish","format-standard","hentry","category-random","tag-fragmented-indexes","tag-indexing-tasks","tag-sql","tag-veam-backup","tag-vmware"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t