locked
SHRINK Database RRS feed

  • Question

  • Hi All,

    We have around 200 (Prod and QA) databases, and my previous DBA created a routine job of SHRINK DATABASE, on some of them, even though it is BAD. Now, he left the company.

    Now, the PROD and QA are slowly getting worse and worse day by day. 

    Is there any way, we can check how much shrink database was done on a database ?

    Saturday, May 24, 2014 1:42 PM

Answers

  • Hi,

    I dont think there is way to check how much shrink database was done you might see job history for job which shinks( that also depends on history settings of the job) database because database would grow if required after every shrink operation neither there is counter to measure how much time shrink was performed or much database actually shrunk is past. Important thing is you realize shrink is evil operation and you should disable all the jobs related to shrink.

    Make sure you keep optimum value for datagrowth for Log and data files , make sure none of the autogrowth is in Percent.Check below link for autogrowth settings

    Understanding Autogrowth and choosing value for autogrowth

    Below is good learning on Shrink Database

    Shrinking growing and redesigning database


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    • Proposed as answer by Uwe RickenMVP Sunday, May 25, 2014 7:58 AM
    • Marked as answer by tracycai Tuesday, June 3, 2014 9:59 AM
    Saturday, May 24, 2014 2:22 PM
  • If the daabases are in full recovery you can get a feel for it by checking the size if the subsequent log backup since shrink is fully logged. Of course, the log backup contains all other odifications as well, so you have to take that into account.

    Tibor Karaszi, SQL Server MVP | web | blog

    • Marked as answer by tracycai Tuesday, June 3, 2014 9:59 AM
    Saturday, May 24, 2014 7:19 PM
  • Hi KG,

    The question for the sum of shrinkages is nonsens because with every shrinking operation you have to deal with the same mess around as with only one time ;). Shrinking a database is the badest idea you may have because... - it is absolutely worthless in a production system (maybe the log) because of several reasons:

    • shrinking a database is a single threaded operation and it may take lot of time
    • shrinking a database means to move data pages from the very last to the next beginning free space of  database.
      http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/
    • shrinking the database makes the indexe a whole bunch of "unordered" pages and index scans will suffer from that.
      http://www.brentozar.com/archive/2013/09/why-index-fragmentation-doesnt-matter-video
    • After the shrink operation you have to rebuild your indexes if your workloads have more index scans than seek.
    • If the system may not "maintain disk volume tasks" the database growth will have a deep impact in the performance of the underlying application because each growth step will be followed by a "zero out" of the newly allocated space. That may cost time depending on the size (I suppose it is 1 MB or 10% :) )

    Stop that and try to implement a resource capacitity management which will help you in the same way as your "customer" ... :)


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)



    • Edited by Uwe RickenMVP Sunday, May 25, 2014 7:54 AM
    • Marked as answer by tracycai Tuesday, June 3, 2014 9:59 AM
    Sunday, May 25, 2014 7:52 AM

All replies

  • Hi,

    I dont think there is way to check how much shrink database was done you might see job history for job which shinks( that also depends on history settings of the job) database because database would grow if required after every shrink operation neither there is counter to measure how much time shrink was performed or much database actually shrunk is past. Important thing is you realize shrink is evil operation and you should disable all the jobs related to shrink.

    Make sure you keep optimum value for datagrowth for Log and data files , make sure none of the autogrowth is in Percent.Check below link for autogrowth settings

    Understanding Autogrowth and choosing value for autogrowth

    Below is good learning on Shrink Database

    Shrinking growing and redesigning database


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    • Proposed as answer by Uwe RickenMVP Sunday, May 25, 2014 7:58 AM
    • Marked as answer by tracycai Tuesday, June 3, 2014 9:59 AM
    Saturday, May 24, 2014 2:22 PM
  • If the daabases are in full recovery you can get a feel for it by checking the size if the subsequent log backup since shrink is fully logged. Of course, the log backup contains all other odifications as well, so you have to take that into account.

    Tibor Karaszi, SQL Server MVP | web | blog

    • Marked as answer by tracycai Tuesday, June 3, 2014 9:59 AM
    Saturday, May 24, 2014 7:19 PM
  • >Now, the PROD and QA are slowly getting worse and worse day by day. 

    REBUILD all indexes with fragmentation over 30%:

    http://www.sqlusa.com/bestpractices2008/rebuild-all-indexes/

    Fragmentation check: http://www.sqlusa.com/bestpractices/dbccshowcontig/


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012




    Saturday, May 24, 2014 8:55 PM
  • Hi KG,

    The question for the sum of shrinkages is nonsens because with every shrinking operation you have to deal with the same mess around as with only one time ;). Shrinking a database is the badest idea you may have because... - it is absolutely worthless in a production system (maybe the log) because of several reasons:

    • shrinking a database is a single threaded operation and it may take lot of time
    • shrinking a database means to move data pages from the very last to the next beginning free space of  database.
      http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/
    • shrinking the database makes the indexe a whole bunch of "unordered" pages and index scans will suffer from that.
      http://www.brentozar.com/archive/2013/09/why-index-fragmentation-doesnt-matter-video
    • After the shrink operation you have to rebuild your indexes if your workloads have more index scans than seek.
    • If the system may not "maintain disk volume tasks" the database growth will have a deep impact in the performance of the underlying application because each growth step will be followed by a "zero out" of the newly allocated space. That may cost time depending on the size (I suppose it is 1 MB or 10% :) )

    Stop that and try to implement a resource capacitity management which will help you in the same way as your "customer" ... :)


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)



    • Edited by Uwe RickenMVP Sunday, May 25, 2014 7:54 AM
    • Marked as answer by tracycai Tuesday, June 3, 2014 9:59 AM
    Sunday, May 25, 2014 7:52 AM
  • >Now, the PROD and QA are slowly getting worse and worse day by day. 

    REBUILD all indexes with fragmentation over 30%:

    http://www.sqlusa.com/bestpractices2008/rebuild-all-indexes/

    Fragmentation check: http://www.sqlusa.com/bestpractices/dbccshowcontig/



    Yes - good tip because it will help for the moment. But the the "doom loop" starts again :)

    After rebuilding the indexes (which will be nearby 100% of the indexes because of the former shrink operation) the database will be shrunken again (hahahaha - a never ending story) because it has been increased by the rebuild operation!


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)

    Sunday, May 25, 2014 7:58 AM