CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2008-r2filesystems
Tim
Tim

Reputation: 8921

When a database is deleted in SQL Server Management Studio, is all space released back to the O/S?

When a database in the Treeview of SQL Server Management Studio is right-clicked and is taken offline and then the Delete option is chosen, is all space allocated to the database released back to the o/s file system pool?

Upvotes: 8

Views: 13886

Answers (2)

Brian Bentley
Brian Bentley

Reputation: 331

If you take the database offline before deleting it, data files will not be deleted from disk. Please see this section of the books online.

http://msdn.microsoft.com/en-us/library/ms178613.aspx

Dropping a database deletes the database from an instance of SQL Server and deletes the physical disk files used by the database. If the database or any one of its files is offline when it is dropped, the disk files are not deleted. These files can be deleted manually by using Windows Explorer. To remove a database from the current server without deleting the files from the file system, use sp_detach_db.

Upvotes: 11

Kenneth Fisher
Kenneth Fisher

Reputation: 3812

Yes and no. As long as all of the database files related to the database are deleted (happens when the delete option is chosen) then yes, that space is now freed back to the OS. However there is some data related to the database in the system databases. The best example is the backup history (which you can choose to delete when you drop the database as well). This doesn't seem like much but the data on several years worth of backups can add up. Particularly if you are doing transaction log backups say every 5 minutes.

Also of course your backup files will still exist and take up space on the drives.

Upvotes: 2

Related Questions