How to free table space

Question

Post reply

How to free table space

Viewing 6 posts - 61 through 65 (of 65 total)

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

Juliet20120 SSC Journeyman Points: 97 More actions · Answer 1

Juliet20120

SSC Journeyman

Points: 97

October 25, 2010 at 10:53 am

#1241748

You can run Truncate table to free space.

miksh Ten Centuries Points: 1344 More actions · Answer 2

Oksana March (10/23/2010)
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.

The table was replicated to several subsrubers so I had to re-init the replications which caused downtime for them (note that we have a real-time data system)

miksh Ten Centuries Points: 1344 More actions · Answer 3

Juliet20120 (10/25/2010)
You can run Truncate table to free space.

Juliet20120, please read the entire post.

miksh Ten Centuries Points: 1344 More actions · Answer 4

Seraj Alam-256815 (10/25/2010)
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.

Thanks for suggestions. Next time we'll try it. So far, we are good.

Johan Bijnens SSC Guru Points: 135382 More actions · Answer 5

miksh (10/21/2010)
After working with Microsoft the workaround was found:
....

Thank you for the great feedback.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

tomconlon7 SSC Enthusiast Points: 122 More actions · Answer 6

Hi,

I tried many things to reclaim deleted data that were recommended but without success (this was due to the nvarchar(max)/LOB_DATA 'bug').

Eventually successfully managed to reclaim 17GB from a 30GB database using:

http://support.microsoft.com/kb/324432

Specifically:

Use a SELECT INTO statement to transfer the whole table to a new table in a different database. Truncate the original table, and then run a DBCC SHRINKFILE statement. Transfer the data back to the original table.

HTH

Tom