Delete DB, where physical files are missing

Question

Delete DB, where physical files are missing

dan.brown1

Ten Centuries

Points: 1301
More actions
October 9, 2017 at 1:17 pm

#333149

Got a situation, the storage location where the physical DB files existing is been deleted by storage team. Now, i'm trying to drop that DB and it is not allowing me to do that
Msg 823, Level 24, State 2, Line 3
The operating system returned error 59(An unexpected network error occurred.) to SQL Server during a read at offset 0x0000000001c000 in file '\\XXXX\CloneShare_1007170355302748\MsSQL\XXX\XXX_primary_01.mdf'.
Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately.
Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
I tried detaching DB, offline and drop. None of them works.
Please help.
October 10, 2017 at 2:51 am

This was removed by the editor as SPAM

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

NJ-DBA SSChampion Points: 13834 More actions · Answer 1

Create a dummy file in another location. Then alter database modify file to change the specification of where the database exists. Restart SQL Server. It will come up suspect. Drop database.

dan.brown1 Ten Centuries Points: 1301 More actions · Answer 2

i tried that, but the alter database modify file is giving same error as mentioned above
ALTER DATABASE sample MODIFY FILE ( NAME = sample_primary_01, FILENAME = "\\XXXX\XXXX\MsSQL\sample\sample_primary_01.mdf")

NJ-DBA SSChampion Points: 13834 More actions · Answer 3

I'm unable to replicate that problem. The syntax you are using is just "drop database [name]" correct?

SQLPirate SSCrazy Points: 2880 More actions · Answer 4

What about something like a Restore with Replace and just restore to a new file location?

dan.brown1 Ten Centuries Points: 1301 More actions · Answer 5

yes, it gives same error for

sp_detach_db 'sample'
GO
or
Use master;
ALTER database sample set offline with ROLLBACK IMMEDIATE;
DROP database sample;

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

dan.brown1 - Monday, October 9, 2017 1:17 PM
Got a situation, the storage location where the physical DB files existing is been deleted by storage team. Now, i'm trying to drop that DB and it is not allowing me to do that
Msg 823, Level 24, State 2, Line 3
The operating system returned error 59(An unexpected network error occurred.) to SQL Server during a read at offset 0x0000000001c000 in file '\\XXXX\CloneShare_1007170355302748\MsSQL\XXX\XXX_primary_01.mdf'.
Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately.
Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
I tried detaching DB, offline and drop. None of them works.
Please help.

Are you able to right click on the databases, go to {properties} and get into files? If so, pick a different file directory and change the database file paths to the new directory. Then try rebooting again..

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

NJ-DBA SSChampion Points: 13834 More actions · Answer 7

dan.brown1 - Monday, October 9, 2017 3:26 PM
yes, it gives same error for
sp_detach_db 'sample'
GO
or
Use master;
ALTER database sample set offline with ROLLBACK IMMEDIATE;
DROP database sample;

Can you drop it without the alter database? Alter will always fail If the db is not accessible. Drop alone should work.

dan.brown1 Ten Centuries Points: 1301 More actions · Answer 8

Drop alone is not working and gives the same error.

Also, right click is giving this error

TITLE: Microsoft SQL Server Management Studio
------------------------------
Cannot show requested dialog.
------------------------------
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
------------------------------
Property MaxDop is not available for Database '[sample]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=13.0.16100.1+((SSMS_Rel_16_5).161130-1812)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=MaxDop&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------

--> Should i try restarting and the DB might go into suspect status, and i can delete it ???

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

dan.brown1 - Monday, October 9, 2017 5:18 PM
Drop alone is not working and gives the same error.
Also, right click is giving this error
TITLE: Microsoft SQL Server Management Studio
------------------------------
Cannot show requested dialog.
------------------------------
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
------------------------------
Property MaxDop is not available for Database '[sample]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=13.0.16100.1+((SSMS_Rel_16_5).161130-1812)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=MaxDop&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
--> Should i try restarting and the DB might go into suspect status, and i can delete it ???

Did you see my post above?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dan.brown1 Ten Centuries Points: 1301 More actions · Answer 10

Here you go.....
I've restarted the SQL services post business hours and intimated users prior the activity. DB is in Recovery Pending status, used drop DB command and it deleted the DB.

Thanks all for your inputs.