Post reply

How to fix the used space of a database?

  • May 23, 2014 at 11:26 am

    #307642

    HI

    I am using sql server 2008 r2 sp 10

    I have a database that originally consumed 500gb, I truncated most of the tables and now the biggest table consume around 20mb

    but the space used says that it is using 242gb with 183gb of free space and it is not even letting me to shrink it, when I try it it completes but there is not change on the size of the datafile

    I already ran DBCC UPDATEUSAGE which only changed a couple things but nothing major

    Any idea on how to reclaim all that space will be welcome

    Thanks!

  • May 23, 2014 at 12:02 pm

    #1716204

    Couple of things...

    Where did you get SP 10? 😉

    Have you rebuilt indexes since truncating?

    Did you have any blobs in those tables that you reduced data?

    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

  • May 23, 2014 at 12:11 pm

    #1716209

    CU 10 🙂

    I already rebuilded all the indexes, even decompressed them, no blobs in there

    I am running a checkdb now

  • May 23, 2014 at 12:16 pm

    #1716210

    ricardo_chicas (5/23/2014)

    HI

    I am using sql server 2008 r2 sp 10

    I have a database that originally consumed 500gb, I truncated most of the tables and now the biggest table consume around 20mb

    but the space used says that it is using 242gb with 183gb of free space and it is not even letting me to shrink it, when I try it it completes but there is not change on the size of the datafile

    I already ran DBCC UPDATEUSAGE which only changed a couple things but nothing major

    Any idea on how to reclaim all that space will be welcome

    Thanks!

    First, shrinking the database is generally a bad idea. Gail Shaw did a great article about this:

    Shrinking databases[/url]

    If the largest table is consuming ~20mb (and there aren't 1000's of tables) then a quick and easy solution would be to:

    1) create a new DB

    2) Script out the DDL for all of your tables and create them in your new db

    3) Insert the data from the old tables into the new tables (INSERT into newdb..table1 SELECT * FROM olddb..table)

    4) Ensure that your data came over correctly then delete the old db.

    Edit/Update: I did not see the other responses when posting this...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • May 23, 2014 at 12:18 pm

    #1716211

    yes I am keeping that one as a last option

    now I am more curious about what happened?, what is corrupted? and how to fix it?

  • May 23, 2014 at 12:31 pm

    #1716218

    Run the script I have on this page and let us know if it reports tables with large space use

    http://jasonbrimhall.info/2014/03/28/table-space-updated-again/

    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

  • May 23, 2014 at 12:36 pm

    #1716219

    SQLRNNR (5/23/2014)

    Run the script I have on this page and let us know if it reports tables with large space use

    http://jasonbrimhall.info/2014/03/28/table-space-updated-again/

    Thanks you!!!

    It was a system table related to service broker

    sysxmitqueue

    It was consuming almost 180gb, I just created a new broker and I was able to reclaim the space

  • May 23, 2014 at 12:38 pm

    #1716220

    ricardo_chicas (5/23/2014)

    SQLRNNR (5/23/2014)

    Run the script I have on this page and let us know if it reports tables with large space use

    http://jasonbrimhall.info/2014/03/28/table-space-updated-again/

    Thanks you!!!

    It was a system table related to service broker

    sysxmitqueue

    It was consuming almost 180gb, I just created a new broker and I was able to reclaim the space

    Excellent - I even have an article on the blog about that very same issue as well (sysxmitqueue).

    In case anybody else is interested in it

    http://jasonbrimhall.info/2012/12/16/on-the-fourth-day/

    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

  • May 23, 2014 at 12:39 pm

    #1716221

    I've found two big problems with the spaceused report. First, if you have Service Broker and you aren't closing the conversations it will take up a huge amount of space that is not reported. Second, if you insert a huge row then update it to be small it still takes up a huge amount of space but reports a small amount; will a re-index fix that?

  • May 23, 2014 at 12:49 pm

    #1716230

    I don't use the spaceused report so am unaccustomed to the those issues but that is good to know.

    In the case of the second issue, generally a re-index will fix that. Occasionally it won't. I don't have conclusive reasons as to when or why.

    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

Viewing 10 posts - 1 through 9 (of 9 total)

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