December 9, 2013 at 9:15 am
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!
December 9, 2013 at 2:18 pm
What are you doing to manage the transaction log? If your database is in Full recovery mode you should have a job that performs a transaction log backup.
Also would you consider just rebuilding the indexes on those fragmented indexes > than some percent of fragmentation?
Just some random thoughts here.
Good luck.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
December 9, 2013 at 3:51 pm
The transaction log will need to be at least as large as your largest table.
December 9, 2013 at 3:54 pm
How much space are you leaving in the datafiles?
You also have to leave room in the data files for the index rebuilds.
December 10, 2013 at 2:10 am
Kurt W. Zimmerman (12/9/2013)
What are you doing to manage the transaction log? If your database is in Full recovery mode you should have a job that performs a transaction log backup.Also would you consider just rebuilding the indexes on those fragmented indexes > than some percent of fragmentation?
Just some random thoughts here.
Good luck.
Kurt
thanks for the random thoughts. 🙂
first step is to put the database in SIMPLE mode and do a full backup.
i normally use the scripts from ola.hallengren.com which checks the level of fragmentation but in this case, this is an SAP database and we do everything according to their best practices, and a reindex of all tables is recommended. secondly (if i understand ola's scripts correctly) the frag check step requires to read the entire table to determine the fragmentation, which adds extra time and i/o. we have a narrow maintenance window and are unsure about the timing.
December 10, 2013 at 2:11 am
arnipetursson (12/9/2013)
The transaction log will need to be at least as large as your largest table.
thanks, that was my assumption.
December 10, 2013 at 2:12 am
arnipetursson (12/9/2013)
How much space are you leaving in the datafiles?You also have to leave room in the data files for the index rebuilds.
planning to leave about 10% free space so will check that it covers the space for index rebuilds. thanks to note that!
December 10, 2013 at 3:12 am
OLDCHAPPY (12/10/2013)
planning to leave about 10% free space so will check that it covers the space for index rebuilds. thanks to note that!
Free space in the data file should be about the size of the largest index. In simple or bulk-logged recovery, the log space needed is less than the size of the index, much less.
You're breaking the log chain to do this, is that a good idea?
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
December 10, 2013 at 3:28 am
GilaMonster (12/10/2013)
OLDCHAPPY (12/10/2013)
planning to leave about 10% free space so will check that it covers the space for index rebuilds. thanks to note that!Free space in the data file should be about the size of the largest index. In simple or bulk-logged recovery, the log space needed is less than the size of the index, much less.
You're breaking the log chain to do this, is that a good idea?
do you mean this?
0 - backup the database 🙂
1 - set the db in simple recovery mode
need to ensure we can recover to the point prior to the "shrink/rebuild process" and restart backups cleanly in full mode after. system will be stopped before the first backup, so no online operations.
i guess i'm a little confused on the backups and switching between full mode and simple mode and back again.
December 10, 2013 at 3:44 am
OLDCHAPPY (12/9/2013)
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
Here I think it's good to remind you on the options for the INDEX REBUILD
... (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
COMPRESSION = PAGE)
ON [Index_FG]
because you said you run a script to generate the indexes definitions. Many scripts do not script indexes well, especially regarding these options. You said you'd run compression, and etc.
Regards,
IgorMi
Igor Micev,My blog: www.igormicev.com
December 10, 2013 at 4:06 am
IgorMi (12/10/2013)
OLDCHAPPY (12/9/2013)
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
Here I think it's good to remind you on the options for the INDEX REBUILD
... (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
COMPRESSION = PAGE)
ON [Index_FG]
because you said you run a script to generate the indexes definitions. Many scripts do not script indexes well, especially regarding these options. You said you'd run compression, and etc.
Regards,
IgorMi
no, i am redefining the indexes. just running the the ALTER INDEX REBUILD statement. this should simply rebuild the index, not redefine it. correct me if i'm wrong! 🙂
December 10, 2013 at 4:13 am
OLDCHAPPY (12/10/2013)
IgorMi (12/10/2013)
OLDCHAPPY (12/9/2013)
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
Here I think it's good to remind you on the options for the INDEX REBUILD
... (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
DATA_COMPRESSION = PAGE)
ON [Index_FG]
because you said you run a script to generate the indexes definitions. Many scripts do not script indexes well, especially regarding these options. You said you'd run compression, and etc.
Regards,
IgorMi
no, i am redefining the indexes. just running the the ALTER INDEX REBUILD statement. this should simply rebuild the index, not redefine it. correct me if i'm wrong! 🙂
Aham, It's OK. You're correct with ALTER INDEX.
Igor Micev,My blog: www.igormicev.com
December 10, 2013 at 4:25 am
Aham, It's OK. You're correct with ALTER INDEX.
i had just ran a single index rebuild in our dev environment to confirm it. it does retain the page compression option. big sigh of relief! 🙂
December 10, 2013 at 4:31 am
OLDCHAPPY (12/10/2013)
GilaMonster (12/10/2013)
OLDCHAPPY (12/10/2013)
planning to leave about 10% free space so will check that it covers the space for index rebuilds. thanks to note that!Free space in the data file should be about the size of the largest index. In simple or bulk-logged recovery, the log space needed is less than the size of the index, much less.
You're breaking the log chain to do this, is that a good idea?
do you mean this?
0 - backup the database 🙂
1 - set the db in simple recovery mode
need to ensure we can recover to the point prior to the "shrink/rebuild process" and restart backups cleanly in full mode after. system will be stopped before the first backup, so no online operations.
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?
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
December 10, 2013 at 4:36 am
OLDCHAPPY (12/10/2013)
Aham, It's OK. You're correct with ALTER INDEX.
i had just ran a single index rebuild in our dev environment to confirm it. it does retain the page compression option. big sigh of relief! 🙂
And I did it for you too, thanks 🙂
Igor Micev,My blog: www.igormicev.com
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply