Shrinking data files

• Aatish Patel

  Ten Centuries

  Points: 1040

  More actions

  January 11, 2014 at 8:07 am

  #396092

  I have SQL 2008 database and have configured mirroring on that database. Now a huge tables with billions rows needs to be truncated and then need to shrink the data file to reclaim space.

  So my question what is the script to shrink the data file only and will shrinking the data file cause or interrupt mirroring?

  Please advise...

  Thank you.

• SQLisAwe5oMe

  SSChampion

  Points: 14047

  More actions

  January 11, 2014 at 8:55 am

  #1679373

  Aatish Patel (1/11/2014)

  ---

  I have SQL 2008 database and have configured mirroring on that database. Now a huge tables with billions rows needs to be truncated and then need to shrink the data file to reclaim space.

  So my question what is the script to shrink the data file only and will shrinking the data file cause or interrupt mirroring?

  Please advise...

  Thank you.

  I faced similar issue recently and when I shrank the db file, it didn't make much difference. So, we had to recreate the cluster index and when I shrinkfile it worked. Not sure if this is the effective way. The only difference between my scenario is that, I was just deleting some rows vs truncating the whole table.

  Let me know how you resolve this issue, just curious. Thanks.

  Regards,
  SQLisAwe5oMe.

• Jeff Moden

  SSC Guru

  Points: 1003865

  More actions

  January 11, 2014 at 2:01 pm

  #1679389

  Aatish Patel (1/11/2014)

  ---

  I have SQL 2008 database and have configured mirroring on that database. Now a huge tables with billions rows needs to be truncated and then need to shrink the data file to reclaim space.

  So my question what is the script to shrink the data file only and will shrinking the data file cause or interrupt mirroring?

  Please advise...

  Thank you.

  First, shrinking the data file will cause massive fragmentation of the B-Tree and Leaf Levels of all indexes including the Clustered Index. You WILL need to at least REORGANIZE (won't rebuild the B-Trees) all indexes once you're done with the shrink.

  I don't know what effect such a truncation would have but I believe that mirroring will not be interrupted. The REORGANIZE of the indexes is likely to have a much more profound effect because it's fully logged in all Recovery Models.

  If that table is likely to get large enough where you need to truncate it again, consider putting it on it's own file group so you don't have to go through a shrink again.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)