Joe Webb

Musing and observations about SQL Server, other technogies, and sometimes just life in general
posts - 85, comments - 857, trackbacks - 0

My Links

News

This blog has moved!

Click here for the new location.

Follow me on Twitter
Add to Technorati Favorites

Search this Blog
 




Archives

Post Categories

About me

Reclaiming unused space from a table

In a recent post, I demonstrated how SQL Server will reuse space made available when rows are deleted from a table. This occurs automatically without any outside intervention on our part.

However under certain circumstances, SQL Server does not automatically reclaim space once used. If a table definition is altered to drop one or more variable length columns, the space consumed by those columns is not immediately made available for reuse by SQL Server. But that's to say that the space is forever lost. We can employee a DBCC utility reclaim the newly-freed space.

To illustrate this behavior and the aforementioned DBCC utility, let's consider an example. Let's create a table with three columns and populate it with some test data as shown in the following code.

USE tempdb ;
GO

--create a test table
CREATE TABLE dbo.Test
(
col1 INT
,col2 VARCHAR(50)
,col3 VARCHAR(MAX)
) ;

--create some test data
DECLARE @cnt INT ;
SET @cnt = 0 ;
WHILE 1 = 1
BEGIN
SELECT
@cnt = @cnt + 1 ;
INSERT
dbo.Test ( col1,col2,col3 )
VALUES (
@cnt
,'test row # ' + CAST(@cnt AS VARCHAR(100))
,REPLICATE('A',@cnt)
) ;
IF @cnt = 1000
BREAK ;
END

 

Now let's view the table to make sure we have what we think we have.

--view the table 
SELECT
*
FROM
dbo.Test ;

Cleantable-2008-01-14a

 

Using a Dynamic Management View, let's see how much space our newly created table is consuming. Note that since this script employs the use of a DMV, it will not work with SQL Server 2000.

--check the size of the table 
SELECT
alloc_unit_type_desc
,page_count
,avg_page_space_used_in_percent
,record_count
FROM
sys.dm_db_index_physical_stats(
DB_ID()
,OBJECT_ID(N'dbo.Test')
,NULL
,NULL,'Detailed') ;

 

On my system, it indicates that 84 data pages are used to store the 1000 rows. Each data page is, on average, 78.4% full.

Cleantable-2008-01-14b

Now let's drop the third column, the one that consumes the most space, and check the space used once again.

--drop the last column
ALTER TABLE dbo.Test DROP COLUMN col3 ;

--check the space used again
SELECT
alloc_unit_type_desc
,page_count
,avg_page_space_used_in_percent
,record_count
FROM
sys.dm_db_index_physical_stats(
DB_ID()
,OBJECT_ID(N'dbo.Test')
,NULL
,NULL
,'Detailed') ;

 

We get the same results - 84 data pages, storing 1000 rows, each 78.4% full - even after dropping the column that consumed the most space.

Cleantable-2008-01-14c

Now, let's reclaim the space using the DBCC CLEANTABLE command and recheck the space consumed.

--reclaim the space from the table
DBCC CLEANTABLE('tempdb', 'dbo.Test') ;

--check the space used one more time
SELECT
alloc_unit_type_desc
,page_count
,avg_page_space_used_in_percent
,record_count
FROM
sys.dm_db_index_physical_stats(
DB_ID()
,OBJECT_ID(N'dbo.Test')
,NULL
,NULL
,'Detailed') ;

 

This time, considerable less space is consumed; the average page is only filled 4.5% full!

Cleantable-2008-01-14d

 

Finally let's clean up.

--clean up
DROP TABLE dbo.Test ;

 

For more information, check out DBCC CLEANTABLE in Books Online.

Cheers!

Joe

kick it on DotNetKicks.com

Print | posted on Monday, January 14, 2008 5:23 PM | Filed Under [ T-SQL SQL Server ]

Feedback

Gravatar

# re: Reclaiming unused space from a table

How can I do this in sql 2000?
1/17/2008 8:15 AM | BobS
Gravatar

# re: Reclaiming unused space from a table

The DBCC CLEANTABLE command is available in SQL Server 2000. It' only the query that calls the DMV that won't work in SQL Server 2000 since DMV's weren't introduced until 2005.
1/17/2008 9:47 AM | Joe Webb
Gravatar

# re: Reclaiming unused space from a table

Thanks for the post. What I found was that this does not shrink the physical .mdf file size. I am facing a problem in production with an audit log table taking up huge space. Tried truncating the table, dbcc shrinkfile etc. But, I am not able to reclaim the physical space.

Do you have any info on this or point me to some resources on the web?

Thanks,
Gulzar
2/27/2008 11:49 AM | Gulzar
Gravatar

# re: Reclaiming unused space from a table

Very didatic... congratulations...
10/1/2008 8:27 PM | ronabrag
Gravatar

# re: Reclaiming unused space from a table

Hi,

Nice posting,
However, I have a question that if table have any indexes does it help to reclaim the space immediately after deleting all the rows from the table using "Delete" command.

I will more illustrate

Having Table xyz occupied space 19 GB having few million rows.

Execute command "delete table xyz" then check table space still 19GB.

I don't have any index on the table.

Does it help to reclaim the space if I create any index on the table?


Regards,
DS
3/20/2010 12:04 AM | Dharmendra Singh Chauhan
Gravatar

# re: Reclaiming unused space from a table

To illustrate this behavior and the aforementioned DBCC utility, let's consider an example. Let's create a table with three columns and populate it with some test data as shown in the following code.


snow boots | snow boots for women | columbia sportswear | columbia sportswear outlet | cheap north face jackets | the north face outlet | mac makeup | cheap makeup
10/19/2010 4:35 AM | furry boots
Gravatar

# love

replica chanel appearance on "Meet the Press" coco chanel earrings that he didn't intend to watches tissot
10/22/2010 3:45 AM | coco chanel clothes
Gravatar

# re: Reclaiming unused space from a table

This super video converter for mac is developed by Emicsoft Studio, it is currently the best video converter running under Mac os x, comparied by isqunite, Visualhub and other Video Converter for Mac Free vide under simple video editing function embedded, support TRIM, CUT, CROP, and Join video files. you may use it as a
10/26/2010 1:39 AM | hanly
Gravatar

# re: Reclaiming unused space from a table

Thanks for your post "This super video converter for mac is developed by Emicsoft Studio, it is currently the best video converter running under Mac os x, comparied by isqunite, Visualhub and other Video Converter for Mac Free vide under simple video editing function embedded, support TRIM, CUT, CROP, and Join video files.....” very amazing, I love it, thanks!
7/28/2011 2:00 AM | http://www.yslshoesvip.com
Gravatar

# re: Reclaiming unused space from a table

always was interested in this theme!
8/2/2011 12:36 AM | registryspeeder
Gravatar

# re: Reclaiming unused space from a table


http://www.ghd-pink-orchid.com ghd straighteners
http://www.nzstraighteners.net ghd
http://www.ghdplanchas-espana.com ghd planchas
http://www.ghdspecials.com GHD South Africa
11/28/2011 2:31 AM | ghd
Gravatar

# re: Reclaiming unused space from a table

Please refer the link
www.sqlservergeeks.com/...
1/3/2012 8:11 AM | Ritesh
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET