February 17, 2007 at 6:57 am
We started rebuilding indexes online (Sql Server 2005 Enterprise Edition) on very big table (around 485 million records). Before we always used offine option and the whole procedure (I mean rebuilding all indexes ) required a bit longer than 2 hours.
Online process is running already for 6 days with unclear perspective. Frankly not sure what to do know- to wait- how long, to kill- roll back can take forever.
I really appreciate if anybody has some experience to share or ideas. Thanks
February 17, 2007 at 11:24 pm
Online indexing is done by creating the index side by side...
I am not sure but I don't think there will any rollback time...
You can simply test it on dev server...
MohammedU
Microsoft SQL Server MVP
February 18, 2007 at 10:21 pm
Thanks for your answer. I do not know regarding possible rollback time but I know for a fact now that online reindexing is not appropriate for a big table under heavy load- it can take forever. In this case we should go for offline reindexing
February 18, 2007 at 10:54 pm
Did you kill the online indexing? how much time it took to rollback?
MohammedU
Microsoft SQL Server MVP
February 19, 2007 at 2:44 am
Yuri,
you could use partitioning for the table and indexes and when rebuild indexes by partition if necessary. Partitons which aren't fragmented don't need to be rebuild. This should shorten the reindex times considerably and also can speed up queries.
Markus
[font="Verdana"]Markus Bohse[/font]
February 19, 2007 at 9:49 am
Thanks guys for your answers.
Markus: we are going to make partitioning on this table, you are absolutely right
Mohamed:I did not kill this procedure- Sql Server did. When tr log became full (DB in Simple recovery mode. I guess due to this very long rebuilding Sql Server did not have a chance to truncate tr log or may be simply lost its mind) rebuilding peacefully gone. But now I am in new trouble. In parallel with this online rebuilding (when this process was still alive) our job where last step was "dbcc checkalloc" stuck. After 5 hours (and normal time for this step is around 20 minutes) I killed this procedure (that eventually put this "dbcc checkalloc" in killed/rollback state). Probaly it was stupid action. Anyhow now our huge DB (around 4 TB) has for 2 days already this damn "killed/rollback" process. I raised the ticket with MS asking for help- how to get rid of this process but besides "wait" ot "reboot server" (not Sql Server service as I can not do this) we have got nothing.
Any comments or ideas are really appreciated.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply