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

Using sp_MSforeachtable and DBCC CLEANTABLE to reclaim space

In a prior posting, I demonstrated how the undocumented sp_MSforeachtable stored procedure can be used to iterate through each of the tables within a database.

In a separate posting earlier this year, I also demonstrated how the DBCC CLEANTABLE command can be employed to reclaim space that was once consumed by variable length character columns that have been subsequently dropped.

In this post, I'd like to combine the two topics and show how the sp_MSforeachtable stored procedure command may be used in conjunction with the DBCC CLEANTABLE command to reclaim space from all tables after one or more columns were dropped.

This technique can be quite handy if you've recently dropped variable length character columns from many or all of your table.

The following script demonstrates this.

USE AdventureWorks ;
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
,'dbo.Test row # '
+ CAST(@cnt AS VARCHAR(100))
,REPLICATE('A',@cnt)
) ;
IF @cnt = 1000
BREAK ;
END



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


 --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') ;



--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') ;



--reclaim the space from the table
EXEC sp_msforeachtable 'DBCC CLEANTABLE(0, ''?'') ';



--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') ;



--clean up
DROP TABLE dbo.Test ;

Foreach_Cleantable-200-01-25

For details on each of the steps, please refer back to the original posts referenced above.

This is not something that you should do as part of your nightly, weekly, or even monthly maintenance plans. It should only be used as needed when the definition of one or more tables has changed.

Cheers!

Joe

kick it on DotNetKicks.com

Print | posted on Friday, January 25, 2008 3:13 PM | Filed Under [ T-SQL SQL Server ]

Feedback

Gravatar

# re: Using sp_MSforeachtable and DBCC CLEANTABLE to reclaim space

do as part of your nightly, weekly, or even monthly maintenance plans. It should on
10/21/2010 9:28 PM | chanel jewelry
Gravatar

# re: Using sp_MSforeachtable and DBCC CLEANTABLE to reclaim space

This technique can be quite handy if you've recently dropped variable length character columns from many or all of your table.

snow boots for women | columbia jackets | mac makeup | the north face outlet
10/23/2010 10:06 AM | north face jackets on sale
Gravatar

# re: Using sp_MSforeachtable and DBCC CLEANTABLE to reclaim space

Really interesting articles. I enjoyed reading it.
http://www.stagecoachgifts.biz/ provides you a beautiful and unique combination of genuine turquoise jewelry set in sterling silver. They offer the finest selection of authentic turquoise jewelry with great jewelry design.
7/8/2011 4:47 AM | turquoise jewelry
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET