CODEWITHSUNDEEP
sql-server
Paulus Viljoen
Paulus Viljoen

Reputation: 91

Error 21 when trying to delete a SQL Server DB

I had a SQL Server database on an external HDD. I forgot to detach the DB. I do not need it anymore, but I am unable to delete or take it off line.

When I try to delete or take the DB offline, I get the following error.

Msg 823, Level 24, State 2, Line 7

The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x00000000012000 in file 'E:\Kenya Air\Monet - Paulus.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 have tried to run a DBCC CHECK, but I get the same error.

Upvotes: 4

Views: 5600

Answers (4)

Jalal Malekpour
Jalal Malekpour

Reputation: 37

I had the same problem, even when I wanted to take the database offline, it gave me this error. But the problem was solved by restarting SQL. sql Image

Upvotes: 0

Sassan Karai
Sassan Karai

Reputation: 1

Do NOT try to set the database offline and back online - this will eventually make things worse.
Stop SQL Server - move the respective database files (data and logfile(s)) to a different location. Start SQL Server again - eventually the DB will indicate (restore pending) - now delete the DB from SQL server. Next attach the database files back to the server and you should be ok - unless the files are physically corrupt. I have seen this problem numerous times - especially on virtualized SQL instances where SQL server is set to autostart and wasn't shut down in a coordinated manner before a system reboot. A momentary connection problem to either the data or log file can cause this problem. In case your system shows this problem more than once set SQL server to start manually.

Upvotes: 0

John VM
John VM

Reputation: 106

Try taking the database offline and then online.

Alter database DatabaseName set offline

Then bring it back online after a while

Alter database DatabaseName set online

Upvotes: 1

James K. Lowden
James K. Lowden

Reputation: 7837

I would try the system stored procedure sp_detach_db in SQL. From the fine manual:

Dropping a database deletes the database from an instance of SQL Server and deletes the physical disk files used by the database. If the database or any one of its files is offline when it is dropped, the disk files are not deleted. These files can be deleted manually by using Windows Explorer. To remove a database from the current server without deleting the files from the file system, use sp_detach_db.

The OS is reporting exactly what you said in your question: In dropping the database, SQL Server attempts to remove the file from a device that no longer exists. Thus the database cannot be "dropped", per definition. But perhaps it can be detached, because that affects only the system's internal definition of the list of available databases.

Upvotes: 0

Related Questions