March 5, 2014 at 2:36 am
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.
March 5, 2014 at 3:07 am
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
March 5, 2014 at 3:31 am
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
March 5, 2014 at 3:41 am
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
March 5, 2014 at 3:46 am
Thank you again. I appreciate you giving your time to help newbies like me develop my understanding.
Regards
Andrew
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply