SQLTeam.com | Weblogs | Forums

How to cancel an SQL reindex rebuild




Using the SQL 2008 Maintenance Plan > Rebuild Index r I’ve scheduled this to run on Sunday at 5:30am against all the database on server . I’m just concerned that when I go in on Monday that it might still be running.
If that’s the case, can anyone advise how it could be cancelled or check how far it need to go before finishing. As it’s a live environment, I will need to restore from the back up if it’s not finished in time for Monday 8am.
Hardware information: The Server is a new Window 2008 32 bit with a maximum ram installed 4GB. I don’t have any more details to hand on the database size etc at the minute as I’m not at work.
Running the rebuild is the equivalent of running this script :-

Kind Regards


You can kill the transaction by finding it in sysprocess/sp_who/Activity Monitor, but please note that the active transaction will have to be rolled back. If it's been running for a few hours, it's going to take a few hours to rollback.

You may want to use REORG instead of REBUILD. REORG does not need to be rolled back like a REBUILD does. REORG can be slower if there's a ton of fragmentation, but it has less impact on the system.


Thanks Tara - next time I'll be using the REORG way - its just that its a long time since a proper rebuild has been done on the system so thought I'd go down this route :smile:



Oh, be careful now, folks. There are a whole bunch of differences between REBUILD and REORGANIZE that you need to be made aware of including the performance differences that Tara spoke of.

For example, for really big tables, you might want to consider temporarily shifting the database to the BULK LOGGED recovery model and then use REBUILD so that you can rebuild the index in a minimally logged fashion. REORGANIZE is ALWAYS fully logged. Also be aware that REBUILD auto-magically rebuilds related stats but won't compact blobs. REORGANIZE doesn't rebuild any stats, compacts blobs (if you rebuild the clustered index or an index with an INCLUDE on a blob), and can always be done online. As Tara mentioned, it does take longer and it also doesn't do as good a job of defragmenting as a good ol' fashion offline REBUILD (can be done online in the Enterprise Edition). Also, as good as it is at defragging, if you do use the BULK LOGGED recovery model, also realize that you won't be able to do a point-in-time restore to the middle of any log file created where a minimally logged operation has taken place. There's also the DROP'n'REBUILD trick to keep the MDF file from blowing out on large table REBUILDs (although I wouldn't recommend that trick for any clustered index or unique index, clustered or not because of potential disabling of FK's and making data totally unavailable until rebuilt... kinda like an OFFLINE evolution).

So REORGANIZE may not actually have less impact especially where log files are concerned. "IT DEPENDS".

There's lot's more to consider before you just make the decision to go one way or another and when. :yum:


There is this other option of rebuild with online = on.

And, also set a fragmentation level thresh hold to only rebuild those tables say above 70%.


Ooooo, again... be careful... My recommendation would be to stay away from ONLINE rebuilds if you can. They end up bloating the MDF file even more than an offline rebuild because they build a non-TempDB working table in the background to capture any possible activity during the online rebuild. Online rebuilds also take longer and don't do as good a job at defragmenting as offline rebuilds do.

See my previous post above for some of the tricks to keep the MDF from exploding.