September 27, 2010 at 12:00 pm
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?
September 27, 2010 at 12:14 pm
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...
September 27, 2010 at 12:46 pm
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?
September 27, 2010 at 12:48 pm
Try a DBCC DBREINDEX(tablename)
September 27, 2010 at 12:48 pm
If the table has 0 rows in it then why not just drop and recreate it to the size you want?
September 27, 2010 at 12:50 pm
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
September 27, 2010 at 12:51 pm
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
September 27, 2010 at 1:13 pm
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.
September 27, 2010 at 1:24 pm
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?
September 27, 2010 at 1:33 pm
CirquedeSQLeil (9/27/2010)
Update statistics and DBCC updateusage.
Well, it didn't help.
September 27, 2010 at 1:40 pm
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
September 27, 2010 at 1:40 pm
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?
September 27, 2010 at 1:41 pm
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
September 27, 2010 at 1:44 pm
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.
September 27, 2010 at 1:50 pm
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