VLDB complete index rebuilds - a little validation needed

Question

VLDB complete index rebuilds - a little validation needed

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

jsqldba SSChampion Points: 11257 More actions · Answer 1

And what happens if the system crashes several hours after that process and the full backup taken after the broken log chain won't restore?

when you refer to the broken log chain, do you mean at the point where we switch from FULL to SIMPLE mode? we don't care about restoring to any point during the SHRINK/REBUILD process. it's all or nothing. restore back to the point before we started it or restore back to the point immediately after it (depends on if the crash seems related to the process).

maybe my description wasn't good...to clarify...

- normally backups are nightly database backups followed by hourly log backups.

- we want to start this process friday night, users will exit the system a little early, all processes stopped. zero activity on the system.

- now, at this point, we do final database backup (like a normal nightly backup) then switch over to SIMPLE recovery mode before starting our SHRINK/REBUILD process.

- run the SHRINK/REBUILD process.

- when finished, switch back to FULL recovery model, and make a database backup and restart the hourly log file backups.

- if we crash 2 or 6 hours later, we restore from the database backup run after the process and then each subsequent log backups.

am i missing something?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

OLDCHAPPY (12/10/2013)
- if we crash 2 or 6 hours later, we restore from the database backup run after the process and then each subsequent log backups.
am i missing something?

Yes. What if that backup run after the process won't restore (it happens)?

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

jsqldba SSChampion Points: 11257 More actions · Answer 3

GilaMonster (12/10/2013)
OLDCHAPPY (12/10/2013)
- if we crash 2 or 6 hours later, we restore from the database backup run after the process and then each subsequent log backups.
am i missing something?
Yes. What if that backup run after the process won't restore (it happens)?

if don't follow you. what if any backup won't restore? you go to the last good backup. in this case, before the shrink process. c'nest pas? what would you do differently?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

OLDCHAPPY (12/10/2013)
you go to the last good backup. in this case, before the shrink process. c'nest pas?

Correct, you go to the last good backup before the shrink, and because you broke the log chain you go no further, all those log backups since are useless and you lose all data since the shrink, however many hours that is.

Is that acceptable?

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

jsqldba SSChampion Points: 11257 More actions · Answer 5

GilaMonster (12/10/2013)
OLDCHAPPY (12/10/2013)
you go to the last good backup. in this case, before the shrink process. c'nest pas?
Correct, you go to the last good backup before the shrink, and because you broke the log chain you go no further, all those log backups since are useless and you lose all data since the shrink, however many hours that is.
Is that acceptable?

i'll have to ask my manager. 🙂 the idea to put in SIMPLE mode was to reduce the constraint on log disk space. log backups are schedule every hour, not sure if that can keep up with the demand by a constant rebuild index process.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Have you considered bulk-logged recovery?

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

jsqldba SSChampion Points: 11257 More actions · Answer 7

GilaMonster (12/10/2013)
Have you considered bulk-logged recovery?

you are right this is probably the way to go. less filling, tastes great!

thanks for your advice. i really appreciate it.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

If you do go that route then you don't need a full backup after the process, just a log backup after switching back to full recovery.

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

arnipetursson SSCertifiable Points: 6557 More actions · Answer 9

For more info on BULK_LOGGED recovery and index rebuilds please see:

http://sqlblog.com/blogs/kalen_delaney/archive/2011/03/08/what-gets-logged-for-index-rebuilds.aspx

ScottPletcher SSC Guru Points: 100946 More actions · Answer 10

You could also consider something like below, particularly since presumably you are on Enterprise Edition and thus can recreate all the non-clustered indexes ONLINE:

Before the shrink:

1) verify you have current scripts for all non-clustered indexes

2) capture sys.dm_db_index_usage_stats and any other metadata you want to keep

3) drop all non-clustered indexes

Shrink:

4) Do the shrink.

After the shrink:

5) recreate all non-clustered indexes

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.