Rebuilding Indexes

Hello all,

I need to diagnose a problem, this Sunday a regular Database Maintenance plan which is supposed to rebuild indexes took exactly 6 hours and 32 minutes. Now thats a hell lot of time and during all that process users were denied access to those tables. This is a production server. I want to know what caused that plan to run for so long and how can I avoid this to happen again plus if it ever happens again how can I make sure that atleast it doesnt lock tables. I know DBCC INDEXDEFRAG doesnt lock tables but how can I make Database Maintenance plan to run DBCC INDEXDEFRAG instead of DBCC DBREINDEX but more importantly why it took 6 hours.

Thanks allThere a lots of reasons that the process could have lasted so long. Do you have any evidence to indicate that this length of time is longer than normal (ie, what did it take last weekend and the weekend before that)?

Some things to possible look for:
1. A transaction that hung
2. Disk space (for both the Data and TLog files)
3. Disk space (for the TempDB)
4. Other jobs or indicators that ran over-long during this period

Also, look at your SQL Error Log for the time period and determine if there are any errors or warnings related to the problem. Look at you your System and Applcation Error Logs for indications that are related to the problem.

As for DBREINDEX vs. INDEXDEFRAG, I might suggest saving the former for quarterly or otherwise routine maintenance periods when you are guaranteed a "clean" box to work on (ie, no user connections, no activity). You can script off a T-SQL that will do an INDEXDEFRAG on specified tables (or all of them if you want).

Regards,

hmscott|||more data? that's usually the number one reason ;)