How to free table space

  • miksh

    Ten Centuries

    Points: 1234

    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?

  • getoffmyfoot

    Hall of Fame

    Points: 3052

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

  • miksh

    Ten Centuries

    Points: 1234

    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?

  • Derrick Smith

    SSChampion

    Points: 10833

    Try a DBCC DBREINDEX(tablename)

  • raistlinx

    Ten Centuries

    Points: 1345

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

  • SQLRNNR

    SSC Guru

    Points: 281210

    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

  • SQLRNNR

    SSC Guru

    Points: 281210

    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

  • miksh

    Ten Centuries

    Points: 1234

    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.

  • getoffmyfoot

    Hall of Fame

    Points: 3052

    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?

  • miksh

    Ten Centuries

    Points: 1234

    CirquedeSQLeil (9/27/2010)


    Update statistics and DBCC updateusage.

    Well, it didn't help.

  • Derrick Smith

    SSChampion

    Points: 10833

    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

  • raistlinx

    Ten Centuries

    Points: 1345

    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?

  • miksh

    Ten Centuries

    Points: 1234

    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

  • miksh

    Ten Centuries

    Points: 1234

    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.

  • Derrick Smith

    SSChampion

    Points: 10833

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

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