SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reboot during large offline clustered index rebuild


Reboot during large offline clustered index rebuild

Author
Message
adlan
adlan
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 96
Hello. Today I had a 3rd party software vendor ask me to reboot a production SQL Server that was busy doing an offline rebuild of a clustered index. The index was very large (approx 80GB - yes it is a SharePoint index) and had been rebuilding for more than 24 hours. The database was in full recovery mode and database index backups were being done every 30 minutes. I declined to do this as I wasn't sure if interrupting an index build in this way would result in database corruption. I am interested in knowing.
1. If people think that this was likely to have caused corruption.
2. If the offline index would have come back on line any sooner by rebooting the server.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215716 Visits: 46270
adlan (3/5/2014)
1. If people think that this was likely to have caused corruption.


No.

2. If the offline index would have come back on line any sooner by rebooting the server.


No, exactly the opposite. SQL would have had to roll back the index rebuild after the restart, with the database unavailable in the recovering state.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


adlan
adlan
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 96
Thanks for your response it is good to know that it could have been done without corruption - I was just being conservative due to the length of the recovery if corruption did occur.
With the database in recovering state, would all of the log backups have been needed to allow SQL to roll back the index? From the lack of free space in the database I don't think that it was building a new index in parallel, so I don't see where else the data for the rollback would be stored.

Regards
Andrew
GilaMonster
GilaMonster
SSC Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215716 Visits: 46270
adlan (3/5/2014)
With the database in recovering state, would all of the log backups have been needed to allow SQL to roll back the index?


No. Since the transaction (the index rebuild) was still active, all the log records needed for the recovery would be in the transaction log, log records cannot be removed if they could still be needed for database recovery

From the lack of free space in the database I don't think that it was building a new index in parallel, so I don't see where else the data for the rollback would be stored.


It was building in parallel, that's how SQL works with index rebuilds, but the rollback data would be in the transaction log, that's the only place it can be.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


adlan
adlan
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 96
Thank you again. I appreciate you giving your time to help newbies like me develop my understanding.

Regards
Andrew
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search