How to free table space

  • Juliet20120

    SSC Journeyman

    Points: 97

    You can run Truncate table to free space.

  • miksh

    Ten Centuries

    Points: 1234

    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: 1234

    Juliet20120 (10/25/2010)


    You can run Truncate table to free space.

    Juliet20120, please read the entire post.

  • miksh

    Ten Centuries

    Points: 1234

    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: 134265

    miksh (10/21/2010)


    After working with Microsoft the workaround was found:

    ....

    Thank you for the great feedback.

    Johan


    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[/url] :alien: but most of the time this is me :hehe:

  • tomconlon7

    SSC Enthusiast

    Points: 122

    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

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

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