SQL Server Performance Forum – Threads Archive
DB Reorg – how to determine when to run
Hi: We have several databases that have maintenance plans set to "Reorganize data and index pages (free space 10%)". This is schedules to run once a week. We are currently running a problem with our backups on the same day due to insufficient disk space which I believe is caused by either the locks placed by the maintenance plan or some other related reason. What are the best practices around running reorg jobs -entire database vs. single tables-regularly scheduled vs. when the conditions require it Thanks
Dila n
Most of the times reorganization cause database grows.
If you don’t have space, I prefer to run the job for the most important tables. Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS†with no warranties for accuracy.
Instead of running against all tables you can run DBCC SHOWCONTIG to identify the fragmented table and then reindex only fragmented once…
You can use the sample script from BOL to get the dbcc showcontig script to against all tables…
MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
You will find some good scripts in this forum that use DBCC SHOWCONTIG to decide if to reindex or not. http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=444 http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1389
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx by all means go through it to know the best practices. IMHO I would suggest to include heavily used tables rather than running the process on the whole database(s). Also you need to identify what is the rootcause of blocking here and simply backup process shouldn’t block other usual process. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Thank you for your suggestions. I found them very helpfull.
]]>