Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to free table space


How to free table space

Author
Message
Whisper9999
Whisper9999
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 344
Did you try DBCC CleanTable out of curiousity?
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21075 Visits: 18259
Whisper9999 (9/28/2010)
Did you try DBCC CleanTable out of curiousity?


He said he did - early on in the thread



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Whisper9999
Whisper9999
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 344
Oops!
miksh
miksh
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 49
Yes, I did DBCC CleanTable. Also, in my earlier results for DBCC SHOWCONTIG the ForwardedRecords=0 as any other field except ScanDensity=100.

I'll try to contact MS and let you know.
getoffmyfoot
getoffmyfoot
Old Hand
Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)

Group: General Forum Members
Points: 344 Visits: 412
If the table has < 2000 rows, then you probably wont notice much performance impact anyway while you drop/recreate indexes; that should take care of it.
miksh
miksh
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 49
After working with Microsoft the workaround was found:

There is a known issue where on a database having a LOB data in it, the SHRINK operation will only shrinks one empty LOB extent at a time. This is by design. And hence we may have to shrink multiple times to release the space to operating system. This behavior is because we do not deallocate the LOB pages; we save them for the next time we may need to insert LOB data. In certain cases, these allocated but empty LOB pages may accumulate. Again, this is by design.

Also When the LOB value to be inserted is larger than one page, we break it up into page-size fragments. And when we insert a page-size fragment, we don't search the existing LOB pages for free space; we instead just allocate a new page. This is an optimization: in the typical case, the free space fragments on the existing pages are all smaller than a page. Since we need a whole page of free space, we might as well skip the search and just allocate a new page.

NOTE: This behavior is by design and have been reported as a known issue.

Considering the above know behavior of SQL , which is by design the work around to release the empty LOB pages is as follows:
Use DBCC CLEANTABLE('databasename', 'tablename') to deallocate all the empty extents.

NOTE: The recommended option to delete all rows of a table is to use TRUNCATE option. Truncate has advantage over the Delete operation as I had mentioned in the previous email.

On your production database backup which I have restored at my end, when I ran the DBCC CLEANTABLE command. The sp_spaceused output after following the workaround was as follows:
Please run DBCC UPDATEUSAGE query on the table before you run SP_SPACEUSED query. Please refer link for more details: http://msdn.microsoft.com/en-us/library/ms188414(v=SQL.90).aspx

ADDITIONAL INFORMATION
• Pages and Extents
http://msdn.microsoft.com/en-us/library/ms190969(v=SQL.90).aspx
• Managing Extent Allocations and Free Space
http://msdn.microsoft.com/en-us/library/ms175195(v=SQL.90).aspx
• Managing Space Used by Objects
http://msdn.microsoft.com/en-us/library/ms187501(v=SQL.90).aspx
Juliet20120
Juliet20120
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 82
Did you try

Truncate table mytable
miksh
miksh
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 49
Yujie Fu (10/22/2010)
Did you try

Truncate table mytable


Yes, it released a table space but in production we did DELELE and stuck with the issue.
Oksana March
Oksana March
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 317
Miksh, I am trying to understand this better, why was dropping and recreating the table not a desirable solution that you left as a "last resort"? I ask because we drop and recreate tables all the time, it causes no problem.
Seraj Alam-256815
Seraj Alam-256815
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 373
Hi,

Did you run "sp_spaceused @updateusage= 'true'"? Hope it fixes.

Alternately, pl try below;

Create a filegroup and aleter table to move to the new file group.
Then again move the table to the original filegroup and remove the temporary file group you created.
This long process is because you do not intend to drop/create an empty table.

I have noticed such problem in SQL 2000 where ntext data type existed and it did not releases the space from table. But the scene was different there as the space were shown in "unused space" which is not the case here. I had to follow the second approach which worked for me.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search