SSMS drop failed for database "Unable to open physical file"

Question

Tried to find this elsewhere with no luck.

I'm trying to drop databases that no longer exist on disk, but still show up in the Object Explorer in SSMS 2014

So how can I 'clean them out' of the Object Explorer?

Do I need to manually remove them from sys.master_files?

The exception was:

    Drop failed for Database 'aspnet-Blawblaw-20141027015559'.  (Microsoft.SqlServer.Smo)

    ------------------------------
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=12.0.2000.8+((SQL14_RTM).140220-1752)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Database&LinkId=20476

    ------------------------------
    Program Location:

       at Microsoft.SqlServer.Management.Smo.SqlSmoObject.DropImpl()
       at Microsoft.SqlServer.Management.Smo.Database.Drop()
       at Microsoft.SqlServer.Management.SqlManagerUI.DropObjects.DoDropObject(Int32 objectRowIndex)
       at Microsoft.SqlServer.Management.SqlManagerUI.DropObjects.DropAllObjects(Boolean stopOnError)

    ===================================

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------
    Program Location:

       at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
       at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
       at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
       at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ExecuteNonQuery(StringCollection queries, Boolean includeDbContext)
       at Microsoft.SqlServer.Management.Smo.SqlSmoObject.DropImplWorker(Urn& urn)
       at Microsoft.SqlServer.Management.Smo.SqlSmoObject.DropImpl()

    ===================================

    Unable to open the physical file "C:\Projects\BlawBlaw\BlawBlaw\App_Data\aspnet-BlawBlaw-20141027015559.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
    File activation failure. The physical file name "C:\Projects\BlawBlaw\BlawBlaw\App_Data\aspnet-BlawBlaw-20141027015559_log.ldf" may be incorrect. (.Net SqlClient Data Provider)

    ------------------------------
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.3000&EvtSrc=MSSQLServer&EvtID=5120&LinkId=20476

    ------------------------------
    Server Name: (LocalDb)\v11.0
    Error Number: 5120
    Severity: 16
    State: 101
    Line Number: 2


    ------------------------------
    Program Location:

       at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
       at Microsoft.Sq

score 2 · Answer 1 · edited Sep 28 '16 at 12:44

2

This helps for me.

Open new Query
Copy the name of the DB (F2)
Run this command: DROP DATABASE [C:TEST.MDF]
-- When "test" is name of the DB
Execute
After refresh, DB string is not shown under databases

edited Sep 28 '16 at 12:44

croxy

4,082
9
28
46

answered Sep 28 '16 at 11:08

vlatko606

909
1
13
21

`Msg 3701, Level 11, State 1, Server COMPUTER\LOCALDB#SHC670BC, Line 1 Cannot drop the database 'C:Test.mdf', because it does not exist or you do not have permission.` – Eric May 25 '17 at 10:33

Eric · Accepted Answer · 2017-05-25T11:18:05.130

2

sqlcmd -S (LocalDB)\MSSQLLocalDB -E -d master -Q "EXEC sp_detach_db 'Test', 'true'"

edited May 25 '17 at 11:18

answered May 25 '17 at 11:03

Eric

2,797
2
20
19

score 0 · Answer 3 · answered Jan 21 '16 at 22:50

steps you would need to run are below:

Open a command prompt
Start the localDb instance if it is not already running: “C:\Program Files\Microsoft SQL Server\120\Tools\Binn\sqllocaldb.exe” start “MSSQLLocalDb″
Drop the localDb database by running the following command: “C:\Program Files\Microsoft SQL Server\120\Tools\Binn\sqlcmd” -S (localdb)\MSSQLLocalDb -E -d master -Q “DROP DATABASE [myDatabase]”
You can stop the localDb service now: “C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqllocaldb.exe” stop “MSSQLLocalDb″

Source: http://kazimnami.azurewebsites.net/techblog/2013/02/27/delete-localdb-database-after-physical-files-have-been-deleted/

`Msg 5120, Level 16, State 101, Server COMPUTER\LOCALDB#SHC670BC, Line 1 Unable to open the physical file "C:\Users\User\Test.mdf". Operating system error 2: "2(The system cannot find the file specified.)". File activation failure. The physical file name "C:\Users\User\Test_log.ldf" may be incorrect.` — Eric, May 25 '17 at 10:34

SSMS drop failed for database "Unable to open physical file"

3 Answers3