IDX rebuild cancel is taking forever - better restart/not cancel?

[TheBugSlayer]

Hi all.

Users started complaining about applications being sluggish. So I look on the SQL Server side and find out that another developer had initiated an index rebuild op and cancelled in the middle as the process took too long. It's now been around 5 hours and the cancellation is not finished yet.

At this point I have two questions:
1-Would it have been better to let the process run to completion (i.e. reindex till it's done)? Maybe it would have completed by now.
2-Is it better to restart the server? What would happen if we did?

Thanks in advance.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[TheBugSlayer]

By IDX I mean index. Sorry.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[gmmastros]

Restarting the server would only cause the process to take longer. When the reindex was canceled, SQL Server started rolling back the data. If you had restarted the server in the middle of a rollback, it would have had to continue rolling back after the server was done restarting.

Would it have been better to let it continue? That's hard to say. It could be that the server was just seconds away from being done when the user canceled the reindex. Or it could be that there was another 10 hours to go.

Did you check for blocking? Personally, I would have run an "sp_who2" and checked the BLK_BY column to see if there was anything causing the reindex to get blocked. It's best to reindex during a maintenance window when nobody is using the server unless you have enterprise edition which supports online reindexing.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheBugSlayer]

Thanks George. I guess in the face of so many unknowns it was best to let it run. It took from around 10AM 4PM! I have weekly and nightly maintenance plans in place...Just don't know why this new employee decided to do that, as we were in the middle of preparing to go live on another lab! I did not get a chance to get involved but I will remember the blocking for the next time.
Thanks again.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[gmmastros]

According to books on line, you need alter table permissions (for the table the index is point to) to be able to recreate the index(es). You could, as the DBA, deny alter table to your devs. This should prevent them from re-indexing.

Of course, I don't know if that's really something you want to do. It is something to consider.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheBugSlayer]

Thanks George. This place runs...differently. One day, I hope, someone would listen to me. Hopefully again it's not as a result of a disaster.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)