It was Friday, evening shift, I got a ticket of Backup job failure on my production database. I login to that server and checked the Job history It was failing due to compression error. I checked the version etc and everything was fine. I decide to take a differential backup with script. It also got failed with error "BACKUP DATABASE is terminating abnormally"
Although this was not related to space issue but still i checked for disk space and there was plenty of free space. I decide to check the error log file and there it was....
"The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x00000000e56000 in file 'H:\MSSQL\Data\XXX.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."
Since the error message says that the drive on which data file resided was not ready, it was too obvious that there was some storage problem. I checked the system event log but did not found any error related to disk. The database was online and accessible. But when I tried to access the Properties of the databases, the same error was thrown. I was not able to see the user tables under the databases. But the other queries like sp_helpfile and select * from sysobjects were executing successfully. When I tried to access a user table by script it was failing with the same error.
I sent an email to storage team and they confirmed that everything was fine from there side.
There were two options with me to fix this issue at that time. 1: Take the database offline and bring it online. 2: Another was restart the SQL server. There were more than one databases on my SQL server instance so I restarted the SQL server service. The database came online and the *device not ready* error message disappeared.
The next action you can take is to run DBCC on your database.
Although this was not related to space issue but still i checked for disk space and there was plenty of free space. I decide to check the error log file and there it was....
"The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x00000000e56000 in file 'H:\MSSQL\Data\XXX.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."
Since the error message says that the drive on which data file resided was not ready, it was too obvious that there was some storage problem. I checked the system event log but did not found any error related to disk. The database was online and accessible. But when I tried to access the Properties of the databases, the same error was thrown. I was not able to see the user tables under the databases. But the other queries like sp_helpfile and select * from sysobjects were executing successfully. When I tried to access a user table by script it was failing with the same error.
I sent an email to storage team and they confirmed that everything was fine from there side.
There were two options with me to fix this issue at that time. 1: Take the database offline and bring it online. 2: Another was restart the SQL server. There were more than one databases on my SQL server instance so I restarted the SQL server service. The database came online and the *device not ready* error message disappeared.
The next action you can take is to run DBCC on your database.
Thanks!!
Great Share! I have also found a good article for SQL Server Error 823 http://www.sqlrecoverytool.com/fix-sql-server-fatal-error-823.html
ReplyDeleteThank You John!!
DeleteThanks Jeff and Johnson, It helped me a lot in my case.
DeleteThanks (2) worked for me. I have a temporary database on an external drive that I use for Development and sometimes forget to detach it before I unplug everything.
ReplyDeleteThis is very common error of SQL database. One reason of this error is corruption in MDF file. To fix this, restore the database from .BAK file. If backup is not up-to-date then, you can repair the MDF file. As Repair_Allow_Data_Loss option of DBCC CHECKDB command could be the reason of data loss so, in that case, you can try SQL recovery software to repair corrupt MDF file.
ReplyDeleteRead more about the software from here: https://www.stellarinfo.com/sql-database-repair.php