Thursday, August 11, 2011

How to resolve "Nonrecoverable I/O error occurred"

Here I am sharing some experience on SQL Server database. While I am performing I/O during a backup or restore operation, I am facing this error message:

“Nonrecoverable I/O error occured on file D:\MSSQL...MDF, Backup database is terminating abnormally.”
If you want full clarification of this error message, keep reading this article:

Root Cause: This is not necessarily that this error occurs due to SQL Server Backup problem. This error is a server side error & have numerous causes such as when system do not have enough space on disk to store the backup , low-level I/O problem or it sounds like bad stripes on the hard drives. This is very bad for SQL Server database mdf file may get suspected or corrupted!

Here is full clarification to rectify this error message:

  • First run DBCC CHECKDB on the database to see what you get for output. It finds 0 errors.
  • After that check out that the backup medium has sufficient space or not.
  • Check that you can connect to the SQL server database or not. If you can't, it is a low-level I/O problem. It is best to discuss this problem with a system administrator.  
  • If this error occurs due to mdf file corruption, use these steps to troubleshoot the issue:
  1. Create a new & blank SQL database with whatever name and filename that you want.
  2. Generate the scripts of all SQL database tables and create them in the new SQL Server database.
  3. Transfer all the data into the new SQL Server database.
  4. Now check out that the same issue occurs with the new database or not. If the issue does not occur any more, you can detach the old SQL Server database, and rename the new SQL Server database to the original name to test.
  5. If the same issue persists again then you should use third party SQL Recovery tool to repair corrupt mdf file.

No comments:

Post a Comment