Menu
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?
992
After you use a DELETE statement in Microsoft SQL Server to delete data from a table, you may notice that the space that the table uses is not completely released.
When you delete the table, you have freed space in the table space. The space is available to the database for your next table (or whatever). You need to either drop or shrink the table space to release the space back to the operating system. A place to start is with dbcc shrinkfile , documented here.
In the database structured query language (SQL), the DELETE statement removes one or more records from a table. A subset may be defined for deletion using a condition, otherwise all records are removed.
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.
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.
157
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.
43
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
