Reclaim Unused Space in a Table

Question

Reclaim Unused Space in a Table

rsteeves-683406

Right there with Babe

Points: 717
More actions
February 3, 2009 at 8:25 am

#126017

I need to reclaim the unused space in specific tables within a database. I have several tables where the unused space is at least 12GB. How do I do that?

Viewing 13 posts - 1 through 12 (of 12 total)

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

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

Assuming you have a clustered index there - PK perhaps? - just reorganize such an index, table reorganizaton would happen as a subproduct of it. 😉

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 2

PaulB elludes to this but doesn't actually make the point clear. Check if the tables have a Clustered Index on them. If they don't, then your unused space problem is likely due to Forwarded Records in the Heap allocation. The only way to get rid of these is the rebuild the table by creating a clustered index and dropping it, or creating a second table with matching schema, inserting all the rows into that new table, dropping the original table, and renaming the new table to the original table name. Building and dropping a clustered index is much easier to do so I'd recommend that route.

I had an experience with this same problem last year. You can read about it on the following post:

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/a58b33b7-3fd5-4fab-b805-074f5f1188d2/

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 3

Jonathan Kehayias (2/3/2009)
PaulB elludes to this but doesn't actually make the point clear.

:w00t: mmhhh... I wasn't avoiding -elluding?- the issue and I think my proposed solution is spot on; don't you agree? 😎

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

rsteeves-683406 Right there with Babe Points: 717 More actions · Answer 4

Thanks, guys. I have a maintenance plan to rebuild indexes once a month, but I see that I should chnage it a weekly process. We are running in about 5000 records a week.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 5

PaulB (2/3/2009)
Jonathan Kehayias (2/3/2009)
PaulB elludes to this but doesn't actually make the point clear.
:w00t: mmhhh... I wasn't avoiding -elluding?- the issue and I think my proposed solution is spot on; don't you agree? 😎

You might have the soup, but you missed the meat and potatoes with your explanation. Something like this you should provide a bit more information than what you have provided. Knowing why a Heap is susceptible to this kind of problem is fairly important because just rebuilding the indexes won't solve the problem on a heap. You have to rebuild the table which would entail building a clustered index, or copying everything into a new table.

The other thing to look at is the fill factors on the indexes. Rebuilding the indexes will reset fill factors back to their specifications, so it is possible that you will have more unused space in the table, after rebuilding the indexes if your fill factors are set very low reserving more free space at the leaf level.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 6

rsteeves (2/3/2009)
Thanks, guys. I have a maintenance plan to rebuild indexes once a month, but I see that I should chnage it a weekly process. We are running in about 5000 records a week.

In todays database environments that is not a lot of change. What version of SQL Server is this on? I understand that this is a SQL Server 2005 forum, but people do as SQL Server 2000 questions here. If it is SQL 2005 what is the output for:

declare @dbid int

declare @objid int

select @dbid = db_id('DatabaseName'), @objid = object_id('DatabaseName.SchemaName.ObjectName')

select i.name, i.fill_factor, s.*

from sys.dm_db_index_physical_stats (@dbid, @objid, default, default, 'DETAILED') as s

join sys.indexes as i on s.object_id = i.object_id and i.index_id = s.index_id

You'll want to change the database and table name out to match your environment. For example, in AdventureWorks it would be:

declare @dbid int

declare @objid int

select @dbid = db_id('AdventureWorks'), @objid = object_id('AdventureWorks.dbo.DatabaseLog')

select i.name, i.fill_factor, s.*

from sys.dm_db_index_physical_stats (@dbid, @objid, default, default, 'DETAILED') as s

join sys.indexes as i on s.object_id = i.object_id and i.index_id = s.index_id

DON'T run this on a large table during peak hours as it will impact performance. The DETAILED report is going to scan every page in the table and give information that the default LIMITED report won't provide.

This will give you the detailed information needed to actually identify your problem points in SQL 2005. If you are in SQL Server 2000, you will need to use DBCC SHOWCONTIG() to figure the same information out.

rsteeves-683406 Right there with Babe Points: 717 More actions · Answer 7

This is SQL Server 2005.

This table has 19.8GB of used space. I tried the Alter Index All on hstordersummary REBUILD and it did not free up the unused space.

When I do execute this script, I see that the tables that have large amounts of unused space do not have a clustered index. Based on what I have read int he replies to my problem, if I add a clustered index, the unused space will "go away". Is that correct?

declare @sql nvarchar(MAX)

create table #usedSpace (

name nvarchar(128),rows varchar(11),

reserved varchar(18),data varchar(18),

index_size varchar(18),unused varchar(18))

declare @table nvarchar(MAX)

declare tabcur cursor for

select s.[name] +'.'+ t.[name] as "TableName"

from sys.tables t

inner join sys.schemas s

on t.schema_id = s.schema_id

order by TableName;

open tabcur;

fetch next from tabcur into @table;

while @@FETCH_STATUS = 0

begin

fetch next from tabcur into @table;

select @sql = 'sp_executesql N''insert #usedSpace exec sp_spaceused ''''' + @table + ''''''''

-- print @sql

-- uncomment if you wish to read the sql statements

exec (@sql)

end;

close tabcur;

deallocate tabcur;

select * from #usedSpace order by cast((left(Data,len(Data)-3)) as int) desc

-- uncomment if you wish to get a recordset

drop table #usedSpace

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 8

rsteeves (2/4/2009)
This is SQL Server 2005.
This table has 19.8GB of used space. I tried the Alter Index All on hstordersummary REBUILD and it did not free up the unused space.
When I do execute this script, I see that the tables that have large amounts of unused space do not have a clustered index. Based on what I have read int he replies to my problem, if I add a clustered index, the unused space will "go away". Is that correct?

Yes, it is a forwarded record problem which will be cleaned up buy adding the clustered index.

rsteeves-683406 Right there with Babe Points: 717 More actions · Answer 9

Adding in the clustered indexes worked great for wiping out the unused space. What I am also seeing is this example:

table rows - 7,262369

table reserved - 13,214,512 kb

table data - 5,809,904

table index_size - 7,404,080

table Unused - 528kb

After applying the indexes, I was hoping that I would see free space appear in the database properties so I could shrink the database. That is not happening. Any additionals thoughts?

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 10

Run DBCC UPDATEUSAGE on the database and check the free space for the database again.

rsteeves-683406 Right there with Babe Points: 717 More actions · Answer 11

Thanks. I executed that. The rows before and after were 6,196,124 and reserved before and after were 12,801,784.

Jeff Moden SSC Guru Points: 1004292 More actions · Answer 12

Jonathan Kehayias (2/5/2009)
Run DBCC UPDATEUSAGE on the database and check the free space for the database again.

Heh... in the spirit of what you told Paul, you should explain why to do this. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)