Reclaim disk space after truncate

Question

Reclaim disk space after truncate

kirti

Old Hand

Points: 387
More actions
September 1, 2021 at 6:12 am

#3925715

Hello,
We have truncated a huge table (50+ million) rows in sql server which was created for testing purpose.
However the disk space is not released back.
Any pointers on how to reclaim the disk space after truncating without shrinking database?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Ant-Green SSC Guru Points: 113445 More actions · Answer 1

If you don't want to shrink the only other alternative is to create a blank database and copy all the data from Original to New then drop the original database.

Johan Bijnens SSC Guru Points: 135255 More actions · Answer 2

You cannot reclaim disk space without shrinking the database, except for tempdb which is being reset to its original size after stop/start of the instance.

Alternative is to create a new filegroup and move all objects to it. ( keep LOBs in mind )

after that, shrinking of the original filegroup should not have that much of an impact.

Of course, best is to create a new filegroup + file(s) if you ever need such "test" table the next time;

Then you can drop the table, empty the file and remove it from the database.

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

ScottPletcher SSC Guru Points: 100947 More actions · Answer 3

You don't need to shrink the database, but you do need shrink the affected file(s). NEVER shrink a database, only file(s).

Run:

DBCC SHOWFILESTATS

and find the FileId(s) with lots of free space. Then issue a shrink on those file(s):

DBCC SHRINKFILE(1, ...however_many_mb_you_want_to_shrink_it_to...)

--DBCC SHINKFILE(3, ...)

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Michael L John One Orange Chip Points: 27038 More actions · Answer 4

If there is a large amount of free space, I would shrink it incrementally in smaller chunks.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

homebrew01 SSC Guru Points: 55539 More actions · Answer 5

Michael L John wrote:

If there is a large amount of free space, I would shrink it incrementally in smaller chunks.

Yes, I was just about to say the same thing.

Or just leave the database alone if you don't really need to recover the space,

This reply was modified 2 years, 7 months ago by homebrew01.

homebrew01 SSC Guru Points: 55539 More actions · Answer 6

Consider the possible negative performance impact of "Shrink" and have a plan to rebuild indexes.

https://www.sqlservercentral.com/forums/topic/slow-performance-after-database-shrink

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

kirti wrote:

Hello,
We have truncated a huge table (50+ million) rows in sql server which was created for testing purpose.
However the disk space is not released back.
Any pointers on how to reclaim the disk space after truncating without shrinking database?

This won't help you now (you need to do the shrink-file thing that others are talking about) but do learn from this lesson. In the future, don't build such a test table on the PRIMARY file group. Build another file group with a single file in it so that when you truncate the table, you don't need to shrink your primary file group. If you want to almost instantly recover the file space from the unused test table, drop it, then drop the file in the file group and then rebuild both. Or drop the table, the file, and the filegroup to be done with it.

You should do the same with a couple of your largest clustered indexes... move each of them to their own file and filegroup. When you need to rebuild them for index maintenance, just create another file and file group, do a CREATE INDEX WITH (DROP_EXISTING = ON) and it'll move the index (the data, it's a CI) to the new file group and you can drop the old one, which gets rid of the extra space that you would have left in the Primary File Group. And THAT process can also be automated. Just don't ever use REORGANIZE for regular index maintenance. It doesn't work the way you probably think it does and it actually does perpetuate fragmentation. It's actually better to not do any index maintenance than it is to do it wrong and using REORGANIZE is wrong in about 97% of the cases.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)