Reclaim disk after drop table in sql server

All we need is an easy explanation of the problem, so here it is.

In sql server, I have dropped table with

DROP TABLE  [tb_tmp]

However from df -h, the disk space has not been increased. What shall I do? I saw something like shrink database. But how do I achieve it with sql command?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

The database files (mdf and ndf) will not reduce in size if you remove data (or object like a table).

The only way (Well you could also create a new DB and move the content from the old to the new and then delete the old one but that add to the complexity) to reduce the size of the data file is to shrink them using

DBCC SHRINKFILE (N'Db_Name' , SizeInMb)

This will cause fragmentation in your database which could, if you do indexes maintenance, cause the DB size to grow again so I would not recommand shrinking the database unless there is a big win or a real need to do it (Ex :if the table you dropped was 200Gb in a 250Gb database).

Otherwise, you leave the database the same size and the future new data will have room so you won’t have to deal with auto-growth later on. This is because when you DROP an object, such as a Table, the space is released internally to the database, to be re-used by future objects. But it is not explicitly released and reclaimed by the server’s disk itself.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply