Post reply

delete records doesn't make the table size shrink

  • March 25, 2012 at 6:52 pm

    #266182

    We have a table in a database which has the most data in the database.

    It basically is a snapshot table to take the student status at the time each night a process run.

    The table storage increases as time goses on, but actually we don't want to store so much student info in the table.

    So we have maintenance job run to delete some data in history.

    But I see the table size still is the same size showing when I right click a table - property- data space.

    What else should I done in order for them table size decrease and hence database size decrease because we deleted a lot of history data in the biggest table?

    Thanks

  • March 25, 2012 at 11:17 pm

    #1464083

    Here you can find an explanation and a possible solution:

    Claim DB Space After Deleting Records in Table - Reduce DB Space

    And here you find some resources why you shouldn't use that solution:

    Stop Shrinking Your Database Files. Seriously. Now.[/url]

    Here's a good reason not to run SHRINKDATABASE...

    Shrinking Database is Bad – Increases Fragmentation – Reduces Performance[/url]

    So, think twice before you act 🙂

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • March 26, 2012 at 4:33 am

    #1464147

    To reduce the table size, rebuild the clustered index. The problem is likely that the deletes have resulted in lots of free space on the pages.

    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

  • March 26, 2012 at 6:45 am

    #1464205

    GilaMonster (3/26/2012)

    To reduce the table size, rebuild the clustered index. The problem is likely that the deletes have resulted in lots of free space on the pages.

    ^^ This

    =============================================================
    /* Backups are worthless, Restores are priceless */

    Get your learn on at SQL University!
    Follow me on Twitter | Connect on LinkedIn
    My blog: http://sqlchicken.com
    My book: Pro Server 2008 Policy-Based Management

Viewing 4 posts - 1 through 3 (of 3 total)

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