Shrink database

  • I received this from a vendor as part of recommended maintenance for sql 2008R2 databases. I always thought it was generally recommended not to shrink growing databases. Is the vendor correct with this procedure?

    "The Shrink Database procedure is required due to continued insertions and deletions in a database table that cause ‘white space’ to develop within the data files. ‘White space’ is used to describe table density or gaps between data. This ‘white space’ may result in more reads required to read through a table as well as making the Statistics and Indexes stale. Shrink database allows the freed space to remain with the files and moves the data to the front of the file space. It removes the ‘white space’ while allowing the remainder of unallocated space to be used for file growth. But once these files are moved to the front of available drive space, statistics and indexes are no longer viable and need to be updated. This is why Shrinking the database can cause SQL latency if not properly performed. However, if properly executed, the SQL performance will improve after shrinking the database.

    So, while running shrink database, users must adhere to the following three factors in order to improve SQL performance:

    a. Retain freed space in database files

    b. Rebuild Indexes

    c. Update Statistics"

    Terrie

  • Terrie (1/3/2013)


    I always thought it was generally recommended not to shrink growing databases.

    You would be completely correct there.

    "The Shrink Database procedure is required due to continued insertions and deletions in a database table that cause ‘white space’ to develop within the data files. ‘White space’ is used to describe table density or gaps between data. This ‘white space’ may result in more reads required to read through a table as well as making the Statistics and Indexes stale. Shrink database allows the freed space to remain with the files and moves the data to the front of the file space. It removes the ‘white space’ while allowing the remainder of unallocated space to be used for file growth. But once these files are moved to the front of available drive space, statistics and indexes are no longer viable and need to be updated. This is why Shrinking the database can cause SQL latency if not properly performed. However, if properly executed, the SQL performance will improve after shrinking the database.

    Errr... That's interesting, but mostly wrong. Continued inserts and deletes can result in low page density (free space on a database page). That low page density does result in more reads been required to read that table (though it doesn't make indexes or stats stale)

    Shrink however does not remove 'white space' within a table that's caused by inserts and deletes. Shrink works at a page level, not within the page. It's an index rebuild or index reorganise that removes that 'white space' within pages.

    A shrink wouldn't make indexes and stats non-viable. It fragments indexes (they're still usable), doesn't do a thing to stats

    No, a shrink (even a 'properly performed' one) will not improve SQL performance. The index rebuild and stats update they recommend would be what improves performance.

    a. Retain freed space in database files

    b. Rebuild Indexes

    c. Update Statistics"

    Rather just do B and C and leave the shrink off completely. It'll take far less time than what the vendor suggests and will have all the good effects and none of the bad ones. And maybe suggest to that vendor that they re-do their research.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Gail, Thanks for you quick reply. Glad to know my gut was right, I just needed validation.

    Terrie

  • GilaMonster (1/3/2013)


    No, a shrink (even a 'properly performed' one) will not improve SQL performance. The index rebuild and stats update they recommend would be what improves performance.

    Even better is that they recommend to shrink and then to rebuild. Let's nevermind that we just shrunk the database, but now we will cause it to grow again when we do the rebuild.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • SQLRNNR (1/3/2013)


    GilaMonster (1/3/2013)


    No, a shrink (even a 'properly performed' one) will not improve SQL performance. The index rebuild and stats update they recommend would be what improves performance.

    Even better is that they recommend to shrink and then to rebuild. Let's nevermind that we just shrunk the database, but now we will cause it to grow again when we do the rebuild.

    Actually not. If you look, they recommend shrink with no_truncate (though they don't say it clearly). That moves all data pages to the beginning of the file, does not release the freed up space back to the OS, so the file size won't change as a result of the shrink, and likely won't change when the rebuild runs

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (1/3/2013)


    SQLRNNR (1/3/2013)


    GilaMonster (1/3/2013)


    No, a shrink (even a 'properly performed' one) will not improve SQL performance. The index rebuild and stats update they recommend would be what improves performance.

    Even better is that they recommend to shrink and then to rebuild. Let's nevermind that we just shrunk the database, but now we will cause it to grow again when we do the rebuild.

    Actually not. If you look, they recommend shrink with no_truncate (though they don't say it clearly). That moves all data pages to the beginning of the file, does not release the freed up space back to the OS, so the file size won't change as a result of the shrink, and likely won't change when the rebuild runs

    I missed on that. But re-reading I see it now. With the recommendations they are providing, how many companies would pick up on that and use the no_truncate option? At any rate, I am curious to know who is making these recommendations.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • So a shrink with no_truncate would improve performance? The company is.... Symantec.

    Terrie

  • Terrie (1/4/2013)


    So a shrink with no_truncate would improve performance?

    No.

    No, a shrink (even a 'properly performed' one) will not improve SQL performance. The index rebuild and stats update they recommend would be what improves performance.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • No, a shrink with no_truncate will "shrink" the data file without actually shrinking it.

    I'm "impressed" that a vendor like Symantec would actually make such recommendations. I mean - you would expect them to have at least one or two actual DBAs working with them, right?


    Kind regards,

    Vegard Hagen
    Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
    @vegard_hagen on Twitter
    Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)

    It is better to light a candle than to curse the darkness. (Chinese proverb)
  • Vegard Hagen (1/4/2013)


    No, a shrink with no_truncate will "shrink" the data file without actually shrinking it.

    I'm "impressed" that a vendor like Symantec would actually make such recommendations. I mean - you would expect them to have at least one or two actual DBAs working with them, right?

    One would think. But this is not surprising.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Gail - As always, I enjoy reading your answers... Thank you.

    you mentioned that by just rebuilding the indexes, that should help... I have to say, I run into same situation, just doing index did not do the trick. We had a VLDB, the vender deleted all history records (DB size was 112 GB, now it is 50 GB). I see that the datafile has over 20 GB of free space. Even though I did backup, and run the index maintenance job still the datafile space size remained the same. I had to do DBCC SHRINKFILE('logfilename', truncateonly) to relase the log space, and still have to do the datafiles! I think there are cases when you need a shrink, even though it is not recommened.

  • lsalih (1/4/2013)


    Gail - As always, I enjoy reading your answers... Thank you.

    you mentioned that by just rebuilding the indexes, that should help... I have to say, I run into same situation, just doing index did not do the trick. We had a VLDB, the vender deleted all history records (DB size was 112 GB, now it is 50 GB). I see that the datafile has over 20 GB of free space. Even though I did backup, and run the index maintenance job still the datafile space size remained the same.

    Index rebuild has nothing to do with releasing space from the data file. Rather it is in place to help with performance.

    I had to do DBCC SHRINKFILE('logfilename', truncateonly) to relase the log space, and still have to do the datafiles! I think there are cases when you need a shrink, even though it is not recommened.

    Occasionally it is necessary to shrink a data file or a database. But you don't shrink for performance reasons.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Gail -

    Yes, I am not doing it to improve performance. It is a one time thing I have to do shrink because large data was deleted and now we have used space to release.

    Once again, thank you.

    Lava

  • lsalih (1/4/2013)


    you mentioned that by just rebuilding the indexes, that should help...

    I never said, suggested or implied that index rebuild would shrink a database file. I said that index rebuilds and stats updates alone (ie without the shrink) would give the performance improvement that the vendor claimed shrink gave (which it does not)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks for all your replies. One shrinks a database to recover space. What does shrink with notruncate accomplish?

    Terrie

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply