Customer has a report they run that shows table space usage. (See query blelow).
Some of these tables are using very little space but have many GBs of unused space . For example the SavedReport table is using 1480KB but has 16461624 of unused space. Is there a way to free up this unused space?
create table #spt_space ( objid int null, rows int null, reserved dec(15) null, data dec(15) null, indexp dec(15) null, unused dec(15) null )
set nocount on
-- Create a cursor to loop through the user tables declare c_tables cursor for select id from sysobjects where xtype = 'U'
fetch next from c_tables into @id
while @@fetch_status = 0 begin
/* Code from sp_spaceused */ insert into #spt_space (objid, reserved) select objid = @id, sum(reserved) from sysindexes where indid in (0, 1, 255) and id = @id
select @pages = sum(dpages) from sysindexes where indid < 2 and id = @id select @pages = @pages + isnull(sum(used), 0) from sysindexes where indid = 255 and id = @id update #spt_space set data = @pages where objid = @id
/* index: sum(used) where indid in (0, 1, 255) - data */ update #spt_space set indexp = (select sum(used) from sysindexes where indid in (0, 1, 255) and id = @id) - data where objid = @id
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */ update #spt_space set unused = reserved - (select sum(used) from sysindexes where indid in (0, 1, 255) and id = @id) where objid = @id
update #spt_space set rows = i.rows from sysindexes i where i.indid < 2 and i.id = @id and objid = @id
from #spt_space, master.dbo.spt_values d where d.number = 1 and d.type = 'E' order by reserved desc
drop table #spt_space close c_tables deallocate c_tables
The “Rebuild Index Task” in a maintenance plan should shrink these tables automatically. However, if you have the “Keep index online while reindexing” checked in the Maintenace Plan, specific tables may NOT be reindexed due to locks. The recommendation is to make sure that “Keep index online while reindexing” is unchecked.
There isn’t a SQL Server shrink command to shrink just a table other than dbcc shrinkdatabase or dbcc shrinkfile which will shrink the entire database. However, dbcc INDEXDEFRAG <tablename> should defrag and shrink the table.
Imported Document ID: HOWTO9423
Subscribing will provide email updates when this Article is updated. Login is required.