November 26, 2012 at 10:33 am
We've archived massive chunk of data from two databases on a SQL instanace. Now want to shrink the database to free up disk space.
If I run DBCC SHRINKDATABASE command for both databases simultaneously would that work? or will block each othere?
November 26, 2012 at 11:09 am
It'll work.
Use ShrinkFile, not ShrinkDB and just shrink the files that have lots of free space in them. Don't shrink as small as possible, leave some free space in the DB. Rebuild your indexes afterwards
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
November 26, 2012 at 3:57 pm
Of course don't rebuild needlessly; check the stats and only rebuild those indexes that require it.
Also, with a reasonably well-tuned tempdb, you should strongly consider using the:
SORT_IN_TEMPDB = ON
option. This can help reduce fragmentation of the data file as well.
Even then, you may get a nasty suprise -- after you rebuild the indexes, the db file might be close to the same size it was originally! So be sure to check after rebuilding.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 26, 2012 at 4:49 pm
ScottPletcher (11/26/2012)
Of course don't rebuild needlessly; check the stats and only rebuild those indexes that require it.Also, with a reasonably well-tuned tempdb, you should strongly consider using the:
SORT_IN_TEMPDB = ON
option. This can help reduce fragmentation of the data file as well.
Even then, you may get a nasty suprise -- after you rebuild the indexes, the db file might be close to the same size it was originally! So be sure to check after rebuilding.
i think "SORT_IN_TEMPDB = ON" is enterprise only (although i may be completely wrong and only an online rebuild is enterprise only) also i would go ahead and rebuild all the indexes after you shrink the DB file as shrinking the DB file may mess with every index in the DB. just make the rebuild time part of the maintenance down time if you can afford it.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
November 27, 2012 at 5:08 am
Thank you all for the helpul/quick inputs:) much appreciated!
November 27, 2012 at 8:52 am
capnhector (11/26/2012)
ScottPletcher (11/26/2012)
Of course don't rebuild needlessly; check the stats and only rebuild those indexes that require it.Also, with a reasonably well-tuned tempdb, you should strongly consider using the:
SORT_IN_TEMPDB = ON
option. This can help reduce fragmentation of the data file as well.
Even then, you may get a nasty suprise -- after you rebuild the indexes, the db file might be close to the same size it was originally! So be sure to check after rebuilding.
i think "SORT_IN_TEMPDB = ON" is enterprise only (although i may be completely wrong and only an online rebuild is enterprise only) also i would go ahead and rebuild all the indexes after you shrink the DB file as shrinking the DB file may mess with every index in the DB. just make the rebuild time part of the maintenance down time if you can afford it.
SORT_IN_TEMPDB = ON does not require Enterprise Edition.
Yes, the shrink may/might have affected all indexes -- or it may/might have affected none, or only a single, small index. Rebuilding a large index that didn't require it is not only a large waste of resources, but it would also increase data space used, reducing the gains from the shrink, which was the whole point to begin with.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply