Free up disk space on sql server

Question

Assuming I have a sql database server with disk space as follows:

D Drive: 10GB (For the MDF file)

E Drive: 10GB (For the LDF file)

And I have only one database on the server with the following details:

Disk space occupied: 9GB MDf file and 9GB LDF file.

10 tables, Only one Schema (dbo), No indexes at all on any of the tables.

What I do: I drop all the tables on the database, but the drive space remains the same(9gb) and I am left with just 1GB on both the drives.

How would I reclaim the space on D drive and what is the best way to do this without shrinking the mdf and without dropping the database? Obviously without adding any disk space.

Shrinking log file would free up space but I am more concerned about getting the space back on the D drive which holds the MDF file.

Answer 1

If you've dropped all the tables, you could just drop the database and re-create it. It will be created with the default sizes and growth rates as defined by the model database. These may not be ideal (I don't find the defaults particularly useful in most scenarios), but you can change them.

Unless you have a lot of things set up on this database (like Service Broker, a ton of users, etc.), I think that is less cumbersome than getting into the habit of growing/shrinking growing/shrinking growing/shrinking. You should put the database on a drive that has enough space to accommodate the data you're going to generate, without having to shrink all the time. Shrinking and growing are both expensive events, especially on slow I/O.

Also, to prevent the log from growing to 9 GB (that seems pretty ridiculous for the data you describe), you should either be full recovery and you need to perform more frequent backups and log backups, or you should switch to simple recovery. More details here - it's long but please read it all.

Answer 2

In SQL server the data and log files only grow automatically. If you deleted a lot of data there will be empty space inside the file but SQL server will not shrink the file automatically because it expects more data to be added, and growing the files is expensive.

Shrinking the file is also generally bad for indexes and stats, but since you dropped all the tables that is not a concern. You can shrink the file by using the DBCC SHRINKFILE command. I'm on my phone so I'll suggest you look up the syntax on the Microsoft knowledge base.