Problem:

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.

Solution:

In this article, we will discuss a simple and fast solution 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.

First, we need to enable the ‘Ole Automation Procedures’ configuration option on your server. This can be done by executing the following commands:

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Ole Automation Procedures', 1
GO
RECONFIGURE
GO

Next, we will create a stored procedure called usp_DetachAndDeleteOfflineDBs. This procedure uses the ‘Ole Automation Procedures’ option for the physical file deletion, so make sure this option is set on for the server configuration.

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

Now, you can use the stored procedure to remove the offline databases and files. For example:

use master
go
exec dbo.usp_DetachAndDeleteOfflineDBs 
go

Notes:

• Test this in a development environment first to make sure you know how this works before using it in a production environment.
• This solution 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 to delete the files from the file system, but enabling only the ‘Ole Automation Procedures’ option is recommended to minimize security vulnerabilities.

By following the steps and using the provided stored procedure, you can easily remove offline databases and their associated files from your SQL Server.