locked
Shrink DB RRS feed

  • Question

  • We have a DB of 450 GB configured with Mirroring

    Every week we are deleting around 25 GB of data in it

    Currently It has around 200 GB of free space in all files

    Should I shrink the database or not?

    Please suggest an advice on this.


    Saturday, November 23, 2013 7:43 PM

Answers

  • Shrinking is a logged operation so all these log records produced by the shrink will have to be shipped to the mirror. Only you can determine how much this disk is worth to you. But chances are that the cost for the shrink process (including the disk required in the two ldf files) is higher than what you are paying today. Not to mention if the files will grow again, which seems likely since you do a weekly delete. More info here: http://www.karaszi.com/SQLServer/info_dont_shrink.asp

    Tibor Karaszi, SQL Server MVP | web | blog

    • Proposed as answer by Heidi-Duan Monday, November 25, 2013 9:46 AM
    • Marked as answer by Allen Li - MSFT Monday, December 2, 2013 8:53 AM
    Saturday, November 23, 2013 7:52 PM
  • We have a DB of 450 GB configured with Mirroring

    Every week we are deleting around 25 GB of data in it

    Currently It has around 200 GB of free space in all files

    Should I shrink the database or not?

    Please suggest an advice on this.


    Hello Sampath,

    Shrinking is single threaded operation which takes huge amount of time to release space and also causes massive logical fragmentation but in fact and as per my experience goes you might have to eventually shrink data files (if you really require space) ,please dont shrink whole database.But as Tibor pointed in his article is causes massive fragmentaion .

    Also delete is fully logged so huge trn logs will be generated ,is your Bandwith (network between primary and mirror) capable of transferring huge logs created due to delete operation if yes you can go ahead , if no this will affect mirroring and the sync between primary and mirror.

    Now instead of deleting huge records you can use below method if it suits you.It will generate less log records

    1. You can transfer records which are required into some new table using below query or Import export wizard.First script out table structure(of old table) query and create a new table with different name and then transfer records

    select * into new_table from old_table

    2. After all required data is transferred into new table truncate old table ,truncate is very minimally logged

    3.Now delete old table which is empty and rename new table to old one this might require some application downtime

    4. Space might not be released intermediately after truncate it might take one day to remove space automatically as it goes in deferred delete process .

    5.If space is not released you can try DBCC SHRINKFILE operation



    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Proposed as answer by Heidi-Duan Monday, November 25, 2013 9:47 AM
    • Marked as answer by Allen Li - MSFT Monday, December 2, 2013 8:53 AM
    Sunday, November 24, 2013 6:09 AM

All replies

  • Shrinking is a logged operation so all these log records produced by the shrink will have to be shipped to the mirror. Only you can determine how much this disk is worth to you. But chances are that the cost for the shrink process (including the disk required in the two ldf files) is higher than what you are paying today. Not to mention if the files will grow again, which seems likely since you do a weekly delete. More info here: http://www.karaszi.com/SQLServer/info_dont_shrink.asp

    Tibor Karaszi, SQL Server MVP | web | blog

    • Proposed as answer by Heidi-Duan Monday, November 25, 2013 9:46 AM
    • Marked as answer by Allen Li - MSFT Monday, December 2, 2013 8:53 AM
    Saturday, November 23, 2013 7:52 PM
  • Thanks for the quick reply Tibor.

    Disk space is a major criteria for me and we have planned to restrict the DB size to 150 GB.

    Saturday, November 23, 2013 8:01 PM
  • Hi

    If so, then you gave the answer to yourself :-)

    * I agree by the way with all that TiborK said, but i think the link not 100% fit your case and is pointing to those who are doing shrink on a regular basis, which is BAD... VARY BAD...

    * Important! use this operation to check the VLF and fix it on the way (virtual log file). most people do not remember to manage the VLF and this is great opportunities to do so (look Goole for "dbcc loginfo") 


    [Personal Site] [Blog] [Facebook]signature

    • Edited by pituachMVP Sunday, November 24, 2013 5:42 AM
    Sunday, November 24, 2013 5:37 AM
  • We have a DB of 450 GB configured with Mirroring

    Every week we are deleting around 25 GB of data in it

    Currently It has around 200 GB of free space in all files

    Should I shrink the database or not?

    Please suggest an advice on this.


    Hello Sampath,

    Shrinking is single threaded operation which takes huge amount of time to release space and also causes massive logical fragmentation but in fact and as per my experience goes you might have to eventually shrink data files (if you really require space) ,please dont shrink whole database.But as Tibor pointed in his article is causes massive fragmentaion .

    Also delete is fully logged so huge trn logs will be generated ,is your Bandwith (network between primary and mirror) capable of transferring huge logs created due to delete operation if yes you can go ahead , if no this will affect mirroring and the sync between primary and mirror.

    Now instead of deleting huge records you can use below method if it suits you.It will generate less log records

    1. You can transfer records which are required into some new table using below query or Import export wizard.First script out table structure(of old table) query and create a new table with different name and then transfer records

    select * into new_table from old_table

    2. After all required data is transferred into new table truncate old table ,truncate is very minimally logged

    3.Now delete old table which is empty and rename new table to old one this might require some application downtime

    4. Space might not be released intermediately after truncate it might take one day to remove space automatically as it goes in deferred delete process .

    5.If space is not released you can try DBCC SHRINKFILE operation



    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Proposed as answer by Heidi-Duan Monday, November 25, 2013 9:47 AM
    • Marked as answer by Allen Li - MSFT Monday, December 2, 2013 8:53 AM
    Sunday, November 24, 2013 6:09 AM