Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Shrink database after archiving Expand / Collapse
Author
Message
Posted Monday, November 26, 2012 10:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 6:51 AM
Points: 232, Visits: 828
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?
Post #1388721
Posted Monday, November 26, 2012 11:09 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
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 2008, MVP
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

Post #1388741
Posted Monday, November 26, 2012 3:57 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:39 AM
Points: 2,332, Visits: 3,511
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1388883
Posted Monday, November 26, 2012 4:49 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 19, 2014 5:16 AM
Points: 887, Visits: 1,774
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 faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler
Post #1388896
Posted Tuesday, November 27, 2012 5:08 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 6:51 AM
Points: 232, Visits: 828
Thank you all for the helpul/quick inputs:) much appreciated!
Post #1389110
Posted Tuesday, November 27, 2012 8:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:39 AM
Points: 2,332, Visits: 3,511
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1389260
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse