Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Reboot during large offline clustered index rebuild Expand / Collapse
Author
Message
Posted Wednesday, March 5, 2014 2:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:13 AM
Points: 22, 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.

Post #1547677
Posted Wednesday, March 5, 2014 3:07 AM This worked for the OP Answer marked as solution


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:08 PM
Points: 40,177, Visits: 36,580
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 2008, MVP
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

Post #1547687
Posted Wednesday, March 5, 2014 3:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:13 AM
Points: 22, 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
Post #1547699
Posted Wednesday, March 5, 2014 3:41 AM This worked for the OP Answer marked as solution


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:08 PM
Points: 40,177, Visits: 36,580
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 2008, MVP
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

Post #1547702
Posted Wednesday, March 5, 2014 3:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:13 AM
Points: 22, Visits: 96
Thank you again. I appreciate you giving your time to help newbies like me develop my understanding.

Regards
Andrew
Post #1547704
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse