October 9, 2017 at 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.
October 9, 2017 at 2:40 pm
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.
October 9, 2017 at 3:08 pm
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")
October 9, 2017 at 3:21 pm
I'm unable to replicate that problem. The syntax you are using is just "drop database [name]" correct?
October 9, 2017 at 3:25 pm
What about something like a Restore with Replace and just restore to a new file location?
October 9, 2017 at 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;
October 9, 2017 at 3:45 pm
dan.brown1 - Monday, October 9, 2017 1:17 PMGot 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 thatMsg 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
October 9, 2017 at 4:57 pm
dan.brown1 - Monday, October 9, 2017 3:26 PMyes, it gives same error forsp_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.
October 9, 2017 at 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 ???
October 9, 2017 at 5:19 pm
dan.brown1 - Monday, October 9, 2017 5:18 PMDrop 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
October 9, 2017 at 8:46 pm
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.
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