Post reply

Unused Table space

  • January 27, 2011 at 12:38 am

    #244365

    i have huge table of 90 GB and through a script i came to know that it has 4Gb of unused space

    My question is how can i reclaim this unused space from the table.

    Also after analysing i have found i have 70GB unused table space in my complete Db

    so how can i reclaim this space

    I have to do it one one table at a time or can i do it on the database level

    NEVER NEVER EVER GIVE UP;-)

  • January 27, 2011 at 1:16 am

    #1278939

    to check your current usage of a table, run this :-

    SELECT alloc_unit_type_desc,page_count,avg_page_space_used_in_percent,record_count FROM

    sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID(N'dbo.Test'),NULL,NULL,'Detailed')

    ** make note of avg_page_space_used_in_percent

    now run

    dbcc cleantable('databasename', 'tablename')

    once it succeed, run the above mentioned select query again, you will see difference in 'avg_page_space_used_in_percent'

    ----------
    Ashish

  • January 28, 2011 at 8:32 am

    #1279730

    I run them and didn't see any differences.

  • January 28, 2011 at 8:41 am

    #1279734

    The DBCC CLEANTABLE is meant to reclain space from dropped varchar columns, not as a maintenace routine. The unused space you are describing is likely due to fragmented data pages. I suggest you rebuild the indexes on that table. You'll likely see some space reclaimed, however with a 90 Gb table, 4 Gb is only 4.44% free space which isn't that much for a table, it's just the table is large so the unused space is large.

    As for doing this on the entire database, I would recommend you create a maintenance plan for rebulding the indexes of your tables. However, be aware if you take that space from the tables, once you start doing more inserts, SQL is going to have to reallocate new data pages which will degrade INSERT performance.

    I hope this helps.

    --------------------------------------------------------------------------
    When you realize you've dug yourself into a hole....Step 1...stop digging.

  • January 28, 2011 at 10:08 am

    #1279810

    It is usually best to let sql server handle using space. Why would you want to 'reclaim' unused space? Isn't it likely to just get requested by the sql storage engine again - thus potentially leading to file growths? I recommend to my clients to set file sizes to allow for what size the database (and tlog) will need to be 12-18 months in the future and set it now. Monitor over time. Nothing good comes from letting autogrowths (especially DEFAULT ones) manage your database file sizes!!!

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • January 29, 2011 at 11:16 am

    #1280100

    I agree with Kevin on this one, but there is one exception. If that table for some reason is a heap when it really shouldn't be a heap. (I can't think of any valid reason for a table that large to be a heap). Creating a clustered index on it will shrink it and free up some space.

    Todd Fifield

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

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