How to remove database from explorer after deleting .MDF and .ldf files

Question

I wanted to know if I can delete or remove the database shown in the object explorer after someone has manually deleted the MDF and ldf files from the database folders.

Answer 1

Are you attempting this with SSMS? I followed this process using SSMS v18.4:

1. Take DB offline
2. Manually delete mdf and ldf files.
3. Right click on database in SSMS and click 'Delete'.

This worked just fine for me.

Answer 2

Refresh Management Console

The first step to perform, is to check that the SSMS (SQL Server Management Studio) is displaying the actual state of all the databases. To achieve this, you would right-click the databases branch in the Management Console and in the context menu select Refresh.

Possible Outcomes

Now depending on the actual state of the database and its files, you will possibly observe one of the following.

1. The Database Vanishes From SSMS

This is the best case, as it was just a refresh issue in (your) SSMS. The database was properly deleted and it was just (your) SSMS that wasn't up-to-date.

2. The Database Is Still Displayed in SSMS

Maybe someone has deleted the (old?) .mdf and .ldf files, but the database is using a different set of files that are located somewhere else.

Run the following command to see where the files are located for the database:

SELECT sdb.name, smf.physical_name
FROM sys.databases AS sdb 
JOIN sys.master_files AS smf 
    ON sdb.database_id = smf.database_id 
    AND sdb.name = 'StackExchange'

Please replace StackExchange with the name of your database

This will provide you with something similar to this:

+---------------+---------------------------------------------+
|     name      |                physical_name                |
+---------------+---------------------------------------------+
| StackExchange | C:\SQL\SQL_DATA\StackExchange.mdf           |
| StackExchange | C:\SQL\SQL_LOGS\StackExchange_log.ldf       |
| StackExchange | C:\SQL\SQL_DATA\StackExchangeRO.ndf         |
| StackExchange | C:\SQL\SQL_DATA\StackExchange_PUBL_SNAP.ndf |
+---------------+---------------------------------------------+

You can then look in those location to see if the database files exist.

Caution
The sys.master_files can have a slight delay before it returns the up-to-date location of the physical files of a database. If you want to see the current up-to-date location of the physical files of a database, then you will have to query the sys.database_files of that database itself.

You can query the file locations of a database using the following query:

USE StackExchange
GO
SELECT sdf.name, sdf.physical_name FROM sys.database_files AS sdf
GO

Please replace StackExchange with the name of your database

This will return the actual locations of the physical database files. Check to see if they exist.

If the files still exist, then your database is running correctly and is still available. This might mean that the database files haven't been deleted and the database has to be dropped if you want to remove it from SSMS.

3. The Database is Displayed with a State

After you have refreshed the database branch, the database might be displayed together with an additional state in brackets:

• OFFLINE
• RESTORING
• RECOVERING
• RECOVERY PENDING
• SUSPECT
• EMERGENCY

^{Reference: Database States (Microsoft | SQL Docs)}

Depending on the state you will have to perform different actions. Please read the referenced document for more information.

---

Worst Case

The Microsoft Windows Server and/or the underlying storage lost its disks, which resulted in the database no longer having any .mdf or .ldf files to write to. the database is running from memory. This will eventually result in a crashed database and it should be displayed as SUSPECT after the SQL Server instance is rebooted. That is, only if the SQL Server instance can be started.

Check the ERRORLOG file of your SQL Server instance for error messages. Check the Windows Event Log for warnings and/or errors regarding disk drives and storage.

Check Database State

If you add a column to the previous query, you should be able to directly check the database state.

Querying master Database

Input

USE master
GO
SELECT sdb.name, smf.physical_name, sdb.state_desc
    FROM sys.databases AS sdb 
    JOIN sys.master_files AS smf 
        ON sdb.database_id = smf.database_id 
        AND sdb.name = 'StackExchange'

Please replace StackExchange with the name of your database

Output

+---------------+---------------------------------------------+------------+
|     name      |                physical_name                | state_desc |
+---------------+---------------------------------------------+------------+
| StackExchange | C:\SQL\SQL_DATA\StackExchange.mdf           | ONLINE     |
| StackExchange | C:\SQL\SQL_LOGS\StackExchange_log.ldf       | ONLINE     |
| StackExchange | C:\SQL\SQL_DATA\StackExchangeRO.ndf         | ONLINE     |
| StackExchange | C:\SQL\SQL_DATA\StackExchange_PUBL_SNAP.ndf | ONLINE     |
+---------------+---------------------------------------------+------------+

Querying User Database

Input

USE StackExchange
GO
SELECT sdf.name, sdf.physical_name, sdf.state_desc
  FROM sys.database_files AS sdf
GO

Please replace StackExchange with the name of your database

Output

+---------------+---------------------------------------------+------------+
|     name      |                physical_name                | state_desc |
+---------------+---------------------------------------------+------------+
| StackExchange | C:\SQL\SQL_DATA\StackExchange.mdf           | ONLINE     |
| StackExchange | C:\SQL\SQL_LOGS\StackExchange_log.ldf       | ONLINE     |
| StackExchange | C:\SQL\SQL_DATA\StackExchangeRO.ndf         | ONLINE     |
| StackExchange | C:\SQL\SQL_DATA\StackExchange_PUBL_SNAP.ndf | ONLINE     |
+---------------+---------------------------------------------+------------+

You can then go from there.

Answer 3

Even if the database status is 'Recovery pending' you should still be able to drop it the regular way:

USE [master] ;
GO
DROP DATABASE [MyDeadDatabase] ;
GO