Post reply

sp_spaceused

  • January 23, 2014 at 8:10 am

    #287550

    SQL Server 2005

    I have several databases that have high amounts of unallocated space like this:

    MYDatabase (dbsize)8037.00 MB(unallocated space)4665.81 MB

    This info was obtained by running sp_spaceused.

    What is the best (safest) way to reclaim this space - or is it even advisable to do so?

  • January 23, 2014 at 9:58 am

    #1682860

    firstly those results are for the total of data and log files, so how big is each?

    the reserved value is of more interest

    ---------------------------------------------------------------------

  • January 23, 2014 at 2:23 pm

    #1682940

    Jpotucek (1/23/2014)

    SQL Server 2005

    I have several databases that have high amounts of unallocated space like this:

    MYDatabase (dbsize)8037.00 MB(unallocated space)4665.81 MB

    This info was obtained by running sp_spaceused.

    What is the best (safest) way to reclaim this space - or is it even advisable to do so?

    If you don't absolutely need the space for something else, I recommend that you leave it alone. The database will use it as it grows.

    --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)

  • January 23, 2014 at 2:33 pm

    #1682945

    You can run the script on the link below to see the sized/used/unused space for each file in the database, and to see the size of the individual tables.

    Script to analyze table space usage

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762

  • January 27, 2014 at 6:34 am

    #1683518

    Jpotucek (1/23/2014)

    SQL Server 2005

    I have several databases that have high amounts of unallocated space like this:

    MYDatabase (dbsize)8037.00 MB(unallocated space)4665.81 MB

    This info was obtained by running sp_spaceused.

    What is the best (safest) way to reclaim this space - or is it even advisable to do so?

    to be honest if this is the extent of the free space problems you have, you'd be better served increasing the volume space for the files. Freeing 4Gb from a database that is only 8GB total is a futile exercise as at some point soon it'll likely grab it back again 😉

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

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

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