By: Eli Leiba | Updated: 2017-11-02 | Comments (2) | Database Administration
When you use the SQL Server DROP DATABASE command for an offline database it only removes the database records from the SQL Server system tables, but does not remove the actual data and log files from the file system. In this tip we look at a simple and fast tool to drop all of the offline databases from your SQL Server and also delete the MDF, NDF and LDF files of these databases from their respective locations in the file system.
I have coded a stored procedure called usp_DetachAndDeleteOfflineDBs. The procedure uses the 'Ole Automation Procedures' option for the physical file deletion, so this option must be set on for the server configuration.
- The procedures queries the sys.master_files system table joined with sys.databases and gets all the physical file names with file paths of all the offline databases and stores it in a temporary table (#filesToDelete).
- The procedure loops over all the offline databases (state value of 6 in sys.databases) and detaches the databases from the SQL Server system by executing sp_detach_db.
- The procedure loops over all the rows of the temporary tables and uses the sp_OAMethod ole automation procedure with the 'DeleteFile' option in order to remove the files from the file system.
- The procedure cleans up the temporary table, closes the cursors and deallocates all objects before finishing.
First we need to enable the 'Ole Automation Procedures' configuration option on your server, which can be done as follows:
EXEC sp_configure 'show advanced options', 1 GO RECONFIGURE GO EXEC sp_configure 'Ole Automation Procedures', 1 GO RECONFIGURE GO
Here is the actual stored procedure to remove the databases.
use master go CREATE PROC usp_DetachAndDeleteOfflineDBs AS BEGIN DECLARE @filesIndex INT = 1; DECLARE @fileCount INT = 0; DECLARE @currdb SYSNAME; DECLARE @currfile VARCHAR(200) DECLARE @Result INT DECLARE @FSO_Token INT DECLARE c_dbs CURSOR FOR SELECT db.NAME FROM sys.databases db WHERE db.STATE = 6; SET NOCOUNT ON CREATE TABLE #filesToDelete ( fid INT identity ,filepath VARCHAR(200) ); INSERT INTO #filesToDelete SELECT mf.physical_name FROM sys.databases db INNER JOIN sys.master_files mf ON db.database_id = mf.database_id WHERE db.STATE = 6; SELECT @fileCount = count(*) FROM #filesToDelete; OPEN c_dbs FETCH NEXT FROM c_dbs INTO @currdb WHILE @@FETCH_STATUS = 0 BEGIN EXEC sp_detach_db @dbname = @currdb FETCH NEXT FROM c_dbs INTO @currdb END CLOSE c_dbs DEALLOCATE c_dbs EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @FSO_Token OUTPUT WHILE @filesIndex <= @fileCount BEGIN SELECT @currfile = filepath FROM #filesToDelete WHERE fid = @filesIndex; EXEC @Result = sp_OAMethod @FSO_Token, 'DeleteFile', NULL, @currfile SET @filesIndex = @filesIndex + 1; END EXEC @Result = sp_OADestroy @FSO_Token DROP TABLE #filesToDelete SET NOCOUNT OFF END GO
Using the Stored Procedure to Remove Offline Databases and Files
I had two offline databases DB1, DB2 on my server. These databases had four files in the C:\SQL\DB Directory. DB1.mdf, DB1.ldf, DB2.mdf and DB2.ldf
After executing the following statements, databases DB1 and DB2 and their respective mdf and ldf files were removed from my system.
use master go exec dbo.usp_DetachAndDeleteOfflineDBs go
- Test this in a development environment first to make sure you know how this works before using in a production environment.
- This was tested with SQL Server 2012 and 2016 Developer editions.
- The 'Ole Automation Procedures' configuration option should be set to true on your server for the above approach to work.
- The 'xp cmdshell' configuration option can also be used in order to delete the files from the file system, but I chose to enable only the 'Ole Automation Procedures' option since in my opinion opening the command shell from SQL Server creates a greater security vulnerability.
- Modify the process to allow you to select one database instead of dropping all of the offline databases.
Last Updated: 2017-11-02
About the author
View all my tips