June 28, 2011 at 1:58 pm
I was running some index defragments on one of our databases this weekend using DBCC INDEXDEFRAG (I know this is being removed in future versions) and it completely ate up the space on my transaction log's drive which caused several unpleasant things to happen.
So i had a couple questions about that, first why it eating up so much transaction log space? And would i see less transaction log space use if i used ALTER INDEX?
June 28, 2011 at 2:34 pm
Reorganise (ALTER INDEX ... REORGANISE or DBCC INDEXDEFRAG) is fully logged always. The more fragmented the index, the more it has to do and the more log space it generates.
Rebuild (ALTER INDEX... REBUILD, DBCC DBREINDEX) is fully logged in full recovery, minimally logged in bulk-logged and simple recovery (for 2005, there were changes in 2008). It always completely recreates the index.
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
June 28, 2011 at 3:03 pm
Okay thanks for the information, I guess I'll have to change our backup plan a little to accomodate it.
June 28, 2011 at 3:09 pm
Make sure that the tran log has space to grow. If it's running out of space during routine maintenance, it needs to be larger.
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
June 28, 2011 at 3:19 pm
Hmmm.. it filled up the entire 135 GB drive we have allocated for the log files in less than an hour, we backup the transaction logs every hour, during normal operations the logs are usually only a couple GB. I was running 8 batchs of index defrags concurrently, which was a first since we're still finalizing our routine maintenance, and i didn't realize they would fill up the logs that fast.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy