January 30, 2018 at 11:03 am
Hi,
For Index Rebuilds, I only find articles about how to do, which I know. What I would like to know is what happens when a rebuild fails. It is hard to find info on the mechanical details.
For instance, consider this scenario:
500 GB hard drive
400 GB data size
60 GB index size
What happens if a rebuild is done? Here's what I expect.
During the rebuild, since a new one is built alongside the existing one, the rebuild job will run out of disk space and fail (since it will need at least 60 GB to build the new one).
What are the implications and things to consider?
Do log files grow while a new index is built? Can these get pretty big?
At the point of rebuild fail, does the DB gracefully and quickly revert back to its state before the rebuild job started? Or will it take some time to backtrack through logs, delete the new index (but this should be quick, yes?), etc? I am mostly worried about the DB being jammed up as I sometimes see during a "trans fail and revert" which puts the DB into "In Recovery" mode.
Also, after the index job fails, once it returns to the "original" state, the DB should still run right? It just does so with a fragmented index ... is this correct?
Feel free to add anything else that may be useful to consider, thanks!
January 30, 2018 at 12:42 pm
SQL Server will rollback the transaction as it always does.
January 30, 2018 at 1:12 pm
Yes it will grow both DB and log files while the process is running, also if it fails it will not automatically free that space up back to the OS(or if it succeeds for that matter), make sure you have enough space on both the data and log drives to finish the operation as that will certainly cause it to fail.
January 30, 2018 at 1:23 pm
Evgeny Garaev - Tuesday, January 30, 2018 12:42 PMSQL Server will rollback the transaction as it always does.
Will the rollback tie up the DB meaning that the DB is not accessible while it cleans itself up?
It is a concern because rollbacks can take some time (depending on the size of the trans log).
January 30, 2018 at 3:44 pm
You should only rebuild a 60GB index if and when you have a really, really compelling reason.
I'd suggest specifying "SORT_IN_TEMPDB = ON" for large rebuilds, unless you have issues with tempdb.
Finally, a 500GB hard drive is rather small. Get another drive(s). They'd probably cost less than the time you'll spend constantly working on this.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 30, 2018 at 7:59 pm
nanonerd - Tuesday, January 30, 2018 1:23 PM...meaning that the DB is not accessible while it cleans itself up?
No. But it can cause some locking. Anyway with a tiny 500Gb database I don't think that you should worry about that. Which edition you are on? Enterprise edition supports online rebuild 😉
January 31, 2018 at 9:54 am
nanonerd - Tuesday, January 30, 2018 1:23 PMEvgeny Garaev - Tuesday, January 30, 2018 12:42 PMSQL Server will rollback the transaction as it always does.Will the rollback tie up the DB meaning that the DB is not accessible while it cleans itself up?
Same as any other transaction rollback, the things being affected by the rollback will be locked.
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
February 1, 2018 at 11:31 pm
nanonerd - Tuesday, January 30, 2018 11:03 AMHi,
For Index Rebuilds, I only find articles about how to do, which I know. What I would like to know is what happens when a rebuild fails. It is hard to find info on the mechanical details.
For instance, consider this scenario:500 GB hard drive
400 GB data size
60 GB index sizeWhat happens if a rebuild is done? Here's what I expect.
During the rebuild, since a new one is built alongside the existing one, the rebuild job will run out of disk space and fail (since it will need at least 60 GB to build the new one).
What are the implications and things to consider?Do log files grow while a new index is built? Can these get pretty big?
At the point of rebuild fail, does the DB gracefully and quickly revert back to its state before the rebuild job started? Or will it take some time to backtrack through logs, delete the new index (but this should be quick, yes?), etc? I am mostly worried about the DB being jammed up as I sometimes see during a "trans fail and revert" which puts the DB into "In Recovery" mode.Also, after the index job fails, once it returns to the "original" state, the DB should still run right? It just does so with a fragmented index ... is this correct?
Feel free to add anything else that may be useful to consider, thanks!
It is a process where the job drop and recreate the indexes on the specified tables. If rebuild index job fails, that means your DB specified tables or
Reporting tables will perform slowly while querying for any data than normal.
Once a Rebuild index job fails, Please make sure to rerun it on another schedule.
February 2, 2018 at 12:45 am
subramaniam.chandrasekar - Thursday, February 1, 2018 11:31 PMIf rebuild index job fails, that means your DB specified tables or Reporting tables will perform slowly while querying for any data than normal.
Why?
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
February 2, 2018 at 2:31 am
GilaMonster - Friday, February 2, 2018 12:45 AMsubramaniam.chandrasekar - Thursday, February 1, 2018 11:31 PMIf rebuild index job fails, that means your DB specified tables or Reporting tables will perform slowly while querying for any data than normal.Why?
We have been running the rebuild job daily in our server. We'd seen in some scenarios where the normal SQL queries will perform slowly when compare d to last day where the rebuild index job had failed. We come across in some situations where your database is purely dedicated for reporting database.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply