locked
Shrink DB RRS feed

  • Question

  • HI All,

         I have situation here for shrinking DB. Our production db is 1.2 TB and actual used space is about 600 GB. The reason we have this gap is we have moved some data to different DB. Now My PM is after me to shrink this DB to 800 GB and release other 400 GB to system.

        What is the best way i can achieve this. i have created a shrink job which shrinks in 100 MB chunks but it does not always shrink. we run this job during our production time as our operation is 24 hrs.

        Any other way to shrink DB or move data around and get db in requested size. and what about fragmentation happens behind the scene.

    Thanks in advance.

    Wednesday, January 16, 2013 2:45 PM

Answers

  • Shrink can create performance issue. You need to defrag your index after shrink. Refer the below link :

    http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/

    The only way to shrink is to use DBCC SHRINKFILE command. We suggest to shrink in small chunks & perform this activity in off bussiness hours. Shrink activity can create blocking like performance issue at run time.


    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    • Proposed as answer by Markwillium Wednesday, January 23, 2013 6:52 AM
    • Marked as answer by Iric Wen Thursday, January 24, 2013 8:42 AM
    Tuesday, January 22, 2013 7:56 PM

All replies

  • Hi,

    If you do not have any specific reason to free up the disk space, do not shrink your database. Please read thsi article about that: http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/ 

    Janos


    There are 10 type of people. Those who understand binary and those who do not.

    My Blog | Hire Me

    Wednesday, January 16, 2013 3:33 PM
  • We have about 600 gb free space that's the reason we are trying to shrink as much as we can.
    Friday, January 18, 2013 4:22 PM
  • Shrink can create performance issue. You need to defrag your index after shrink. Refer the below link :

    http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/

    The only way to shrink is to use DBCC SHRINKFILE command. We suggest to shrink in small chunks & perform this activity in off bussiness hours. Shrink activity can create blocking like performance issue at run time.


    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    • Proposed as answer by Markwillium Wednesday, January 23, 2013 6:52 AM
    • Marked as answer by Iric Wen Thursday, January 24, 2013 8:42 AM
    Tuesday, January 22, 2013 7:56 PM
  • Hi there, I think you need to look at the data growth as well. How long will it take to fill up that 400gb that you will get back. If data growth is exponential, there is no benefit to shrinking your db file. We had a 550gb db which was mostly taken up by a 350gb audit table. We decided to archive audit records older than 3 months to text files and did a once off db shrinkfile and cleared about 300gb. We now have a regular maintenance plan to archive and delete audit records older than 3 months which helps keep the db to a reasonable size. Yes, you need to rebuild your indexes after the shrink process. However we have not done so yet but no users are complaining. Thong
    Thursday, January 24, 2013 2:46 AM