locked
Can I shrink the database for database mirroring RRS feed

  • Question

  • Hi All, 

    Currently I am having a database with size 130GB and mirroring had been set. There are some tables which would like to be deleted and can I know whether I can free up some space (shrinking the database) after I delete the tables ? Thanks. 

    Best Regards, 

                  Han

    Monday, June 24, 2013 11:49 PM

Answers

  • You can of course shrink the files, even with mirroring enabled. The shrink-file command will be propagated to the mirror just like any other command. I have to agree with Srinivasan though, shrinking your files is something you should only do if you are low on diskspace...
    • Proposed as answer by Uri DimantMVP Tuesday, June 25, 2013 10:49 AM
    • Marked as answer by Fanny Liu Saturday, June 29, 2013 8:48 AM
    Tuesday, June 25, 2013 7:39 AM

All replies

  • There are some tables which would like to be deleted and can I know whether I can free up some space (shrinking the database) after I delete the tables ? . 

     Han

    Are you going to delete some rows or full table. If full table, then TRUNCATE will release space immediately.

    Shrink database/File is not advised unless you are really running out of disk space since it will result more fragementation and shrink will take lots of resources .

    Do you need to claim space?  Can you post your disk drive space?

    When you shrink principal database, it will be automatically applied to mirrored database.


    Srinivasan

    Tuesday, June 25, 2013 3:57 AM
  • Probably you need to drop the mirroring ---shrink the principal ---re-establish the mirroring...

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Tuesday, June 25, 2013 7:17 AM
  • You can of course shrink the files, even with mirroring enabled. The shrink-file command will be propagated to the mirror just like any other command. I have to agree with Srinivasan though, shrinking your files is something you should only do if you are low on diskspace...
    • Proposed as answer by Uri DimantMVP Tuesday, June 25, 2013 10:49 AM
    • Marked as answer by Fanny Liu Saturday, June 29, 2013 8:48 AM
    Tuesday, June 25, 2013 7:39 AM
  • Hi PrinceLucifer.

    I always wanted test this scenario. So, you say if the principal and mirroring accordingly have each 100 gb db size  and I succeed to shrink the Principal to 5.5 GB, does it mean that my mirroring will be 5.5GB as well  ,am I right?

    Thanks 


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    • Proposed as answer by Uri DimantMVP Tuesday, June 25, 2013 10:49 AM
    • Unproposed as answer by Uri DimantMVP Tuesday, June 25, 2013 10:49 AM
    Tuesday, June 25, 2013 8:44 AM
  • Right as always Uri.

    Tuesday, June 25, 2013 9:17 AM
  • Thanks, you know, I am far away from your knowledge of the subject :-)

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Tuesday, June 25, 2013 10:49 AM
  • Hi Srinivasan, 

    Yes. Currently the drive left around 15% free space, so I would like to drop those tables which no longer use to free up the space. 

    Wednesday, June 26, 2013 11:41 PM