Reclaim disk after drop table in sql server [duplicate]

Question

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?

Answer 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.