SQL Server Performance Forum – Threads Archive
OverHead by Re-organize Index
Hello In my maintenance plan of 2005 , I have the Re-organize Index everynight . And in the morning I see the T-Log has grown huge. Does the Re-Organize of Index cause the T-Log to grow and does this hinder the Server perfomance when the task is taking place. ThanksRe-organing is important, but when it does run, it does use transaction log space and it does use up resources, including locking tables during the process. That is why it is important to perform this step during a period of minimal activity. Generally speaking, re-organizing can be done once a week or so, unless your database is highly active and changes a lot each day. —————————–
Brad M. McGehee, SQL Server MVP
Instead of reindexing daily as bradmcgehee mentioned you can do weekly or monthly… Do the reindexing only fragmented tables… Check the fragmentation before doing the reindexing… You can use the script from MS from the following article… http://msdn2.microsoft.com/en-us/library/ms188917.aspx
Mohammed U.
Check this link for the reason why Tlog grew during the reindex
http://www.sql-server-performance.com/faq/sqlviewfaq.aspx?faqid=149 I have a question for SQL Server Gurus DBCC DBREINDEX
DBCC INDEXDEFRAG
CREATE INDEX
DROP INDEX Did you find some different behavors for the above commands on 2005 databases? ——————
Bug explorer/finder/seeker/locator
——————
Yes there is a difference in DBREINDEX, CREATE INDEX as compared to SQL 2000, we are working on various articles on SQL 2005 which you will find the relevant information on this website soon. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
]]>