CODEWITHSUNDEEP
sql-servert-sql
pawan-----SQLServer
pawan-----SQLServer

Reputation: 73

SQL Server - dropping table doesn't reduce size of database

I am not able to see memory released in my HDD when IOdrop the table using drop commmand.

I am running short of memory. How to drop tables that relieves memory to the OS?

thanks

Upvotes: 0

Views: 2930

Answers (1)

JNK
JNK

Reputation: 65217

I'm assuming here you mean hard drive space.

This is not how storage works with SQL Server. SQL basically tells the OS "I need this much space for my database files" and reserves that much space. The exact size depends on a lot of factors, including how it was initially created, autogrowth settings, etc.

After that it's up to SQL to maintain the database inside the files that it has. It can grow files automatically (Autogrow), but as a rule it will not shrink files automatically. Shrinking can cause issues with fragmentation, and is a very IO intensive process. This has to be run manually.

In short, the only way you can reclaim that space is to shrink your DB. If you absolutely MUST do this, do it during down time, and make sure you shrink your log file first since you may be able to recover enough space just by doing that.

Upvotes: 2

Related Questions