Home Forums SQL Server 2012 SQL 2012 - General How to check the size of the database after implemeting data compression across all tables RE: How to check the size of the database after implemeting data compression across all tables

  • May 22, 2015 at 2:11 pm

    #1800145

    parag.sv (5/22/2015)

    Hi, I guess everyone's suggesting be careful before using compression but no one's answering your question.

    Yes, it will show the database size same as it was before you compressed all the tables inside it- as you have not reclaimed the space released by your data compression.

    Run following: e.g. if your database name is 'Staging', it will be as follows

    DBCC SHRINKDATABASE (Staging)

    GO

    Right click the database and check under properties to see the updated DB size.

    Please note: Shrinking the database will severely fragment its contents. This is single threaded operation and might take long time to complete depending on how big your database is.

    Took me 1 min 20 secs for DB size of 25 GB DB (All the tables inside the DB were already compressed)

    Hope that helps!!

    I believe that Gail's comment on it not changing the size of the database did answer the question.

    I'd also recommend that if you do tell someone to shrink the database (thanks for including the warning there), you be very explicit as to how because there is no need to shrink the logfile in the process and they will need to rebuild all indexes on tables large enough where an index matters.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)