ok, first of all, VLDB is relative. it's a 1.4TB database so that's VLDB for me.
so what i'm tasked to do it shrink a large database and part of the process is to rebuild all indexes afterwards in order to defragment the database caused by the shrink process. i know, i know, you should never shrink but in this case we must. the database was oversized in the first place (about 2x larger than needed) and then we compressed the database during the 2008 -> 2008 R2 upgrade so now after compression and the oversizing, we are running with about 40% space used in the db, even after several years of growth. the db is running on a SAN and they (managers) want to reclaim this (expensive) SAN space for other uses. (plus multiply this database by about 6 between TEST, DEV, QA, and other system copies). i've already run the shrink process on one of the smaller DEV environments but not the reindex part (missed that point at the time). the shrinks alone took 16 hours so we are looking at an all weekend job.
SOOOO...i'm getting ready a SQL jobs broken down like this:
0 - backup the database
1 - set the db in simple recovery mode
2 - turn off disallow page locks on certain tables
3 - do the shrinks, one datafile at a time (with emails in between each to chart our progress over the weekend)
4 - reindex all
5 - turn on disallow page locks on certain tables
6 - set the database in full recovery mode
firstly, anyone see anything wrong with this process? just to doublecheck my thought process.
secondly, a question about the ALTER INDEX commands. there are 78,000+ tables. i ran a script to generate all the index rebuild commands like this:
ALTER INDEX ALL ON dbo.TABLE1 REBUILD
ALTER INDEX ALL ON dbo.TABLE2 REBUILD
ALTER INDEX ALL ON dbo.TABLE3 REBUILD
ALTER INDEX ALL ON dbo.TABLE783863 REBUILD
i'm setting my database in SIMPLE mode because AFAIK index rebuilds are logged transactions so i don't want to blow up my system for lack of space in the dblog file. when SQL rebuilds a large index (say 70GB, my largest table) are the entire index transactions written to the dblog? or is it broken up by checkpoints? in other words, do i need to be concern with the dblog space during the index rebuild process?
thanks for any advice!