How to free table space

  • Hi all,

    Below is the result when we run the sp_spaceused for a table:

    name rows reserved data index_size unused

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

    mytable 0 8441576 KB 8439672 KB 0 1864 KB

    As you see a table has 0 rows and 0 index size, but the reserved/data size is huge. How to reduce this table size?

    We tried to re-index the table, and used the DBCC cleantable to reduce the size, but the reserved size wasn’t changed. We cannot truncate the table because it is one of the replicated tables. Is there a way to reduce the reserved size (it’s really an empty table) without truncating it?

  • You could do a DBCC shrinkdb... but that would be a database-wide shrink which may not be desirable.

    You could also drop/create table...

  • Hmm.... very brutal solution. Anything else possible for SQL 2005?

    Btw, Shrink Dtabase/File Task dialog in Mgmt Studio shows that the only 3GB could be freed but that table has 8GB free. Why? Could shrinking really do the trick?

  • Try a DBCC DBREINDEX(tablename)

  • If the table has 0 rows in it then why not just drop and recreate it to the size you want?

  • raistlinx (9/27/2010)


    If the table has 0 rows in it then why not just drop and recreate it to the size you want?

    Downstream effects can be brutal.

    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

  • Did you recently perform a delete operation on this table that removed all records? Or ever?

    Try doing an index rebuild for the indexes on the table. Update statistics and DBCC updateusage.

    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

  • CirquedeSQLeil (9/27/2010)


    Did you recently perform a delete operation on this table that removed all records? Or ever?

    Yes we did. Recently we kept data allocating ~1.5GB and now decided to delete all the records daily using DELETE.

    Try doing an index rebuild for the indexes on the table. Update statistics and DBCC updateusage.

    We rebuilt all the indexes and updating staistics daily as well as tried DBCC CLEANTABLE but nothing helped. We'll try DBCC updateusage, thanks for advice.

  • Yea, the drop/create table is brutal... however what about drop/creating all the indexes if they won't shrink? if the table has 0 rows that seems like a pretty safe option, no?

  • CirquedeSQLeil (9/27/2010)


    Update statistics and DBCC updateusage.

    Well, it didn't help.

  • Run this and see if there's an index using more space than the others (significantly, anyway). Replace the tablename_here with your table name

    select

    '['+DB_NAME(database_id)+'].['+c.name+'].['+d.name+']' as [DB.Table]

    ,b.name as [Index Name]

    ,page_count / 8 as [Size (KB)]

    FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('TABLENAME_HERE'), NULL, NULL , NULL) a

    INNER JOIN sys.indexes b ON b.object_id = a.object_id AND b.index_id = a.index_id

    INNER JOIN sys.objects d on d.object_id = a.object_id

    INNER JOIN sys.schemas c ON d.schema_id = c.schema_id

  • getoffmyfoot (9/27/2010)


    Yea, the drop/create table is brutal...

    Maybe I am missing something here, could someone elaborate on why this is not good in this situation?

  • getoffmyfoot (9/27/2010)


    Yea, the drop/create table is brutal... however what about drop/creating all the indexes if they won't shrink? if the table has 0 rows that seems like a pretty safe option, no?

    As you can see from sp_spaceused results the indexes occupy 0 bytes. We was able to reproduce the issue on the test environment and as per your advice recreated indexed (one of them was clustered PK) but it didn't help.

    Still trying to find a good solution otherwise we'll try to:

    - truncate table - requires replication re-init which is quite painful process

    - or as a last resort to shrink db. The only concern is that srinking releases the unused space which is based on sp_spaceused 1864 KB while reserved space is 8441576 KB and data space is 8439672 KB

  • Derrick Smith (9/27/2010)


    Run this and see if there's an index using more space than the others (significantly, anyway). Replace the tablename_here with your table name

    select

    '['+DB_NAME(database_id)+'].['+c.name+'].['+d.name+']' as [DB.Table]

    ,b.name as [Index Name]

    ,page_count / 8 as [Size (KB)]

    FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('TABLENAME_HERE'), NULL, NULL , NULL) a

    INNER JOIN sys.indexes b ON b.object_id = a.object_id AND b.index_id = a.index_id

    INNER JOIN sys.objects d on d.object_id = a.object_id

    INNER JOIN sys.schemas c ON d.schema_id = c.schema_id

    This scripted returned two indexes both with size 0 KB.

  • I've never seen a table that stubborn before. Was hoping that would return different results than sp_spaceused, and it would have just been an issue with sizes not updating.

    Have you tried doing ALTER TABLE tablename REBUILD ?

Viewing 15 posts - 1 through 15 (of 65 total)

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