SQL Server Performance Forum – Threads Archive
DBCC Reindex and trans log
Hi,I recently added a job to our production server that rebuilds all the indexes on our database in the early hours of Friday morning. This job seems to be working fine…this week i changed our backup plan to include regular transaction log backups, and this too seems to be working fine… To make sure the new backup job are working, i have been monitoring this every day, and restoring from the backups every couple of days. This morning i noticed that the transaction log backup from just after the reindexing occurred is over 1gb (meaning it had to grow several times)! Am i right in thinking that DBCC DBREINDEX is a logged operation? any suggestions on how i can keep the size of the log down during the reindexing process?? CHeers,
Ben ‘I reject your reality and substitute my own’ – Adam Savage
hi benwilson,<br /><br />Its very true that after running dbcc dbreindex , T-Log is increased because its a <b>Logged Operation</b> , previously i have noticed the same at my end also ….to reduce t-log size i execute dbcc shrinkfile after dbcc dbreindex but during dbcc dbreindex ‘its not possible to keep the log down during reindex process’ – Its my feeling.<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">DBCC DBREINDEX can rebuild all of the indexes for a table in one statement, which is easier than coding multiple DROP INDEX and CREATE INDEX statements. Because the work is done by one statement, DBCC DBREINDEX is automatically atomic, while individual DROP INDEX and CREATE INDEX statements would have to be put in a transaction to be atomic. Also, DBCC DBREINDEX can take advantage of more optimizations with DBCC DBREINDEX than it can with individual DROP INDEX and CREATE INDEX statements.<br /><br />DBCC DBREINDEX is not supported for use on system tables. <hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"> <br /><br />Please read this article in this regards : <br /><br /><b><a href=’http://www.windowsitpro.com/Article/ArticleID/41844/41844.html?Ad=1′ target=’_blank’ title=’http://www.windowsitpro.com/Article/ArticleID/41844/41844.html?Ad=1′<a target="_blank" href=http://www.windowsitpro.com/Article/ArticleID/41844/41844.html?Ad=1>http://www.windowsitpro.com/Article/ArticleID/41844/41844.html?Ad=1</a></a></b><br /><b><a href=’http://support.microsoft.com/?kbid=873235′ target=’_blank’ title=’http://support.microsoft.com/?kbid=873235′<a target="_blank" href=http://support.microsoft.com/?kbid=873235>http://support.microsoft.com/?kbid=873235</a></a></b><br /><br />HTH<br /><br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]<br />Regards.<br /><br /><br /><br /><br />hsGoswami<br />[email protected]
Thanks…seems i am doomed to have the log grow and have a huge transaction log backup if i stick with DBCC DBReindex! ‘I reject your reality and substitute my own’ – Adam Savage
Check this KBAhttp://support.microsoft.com/?kbid=873235 for relevancy. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Check this out:http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
]]>