Error 822

Severity Level 20

Message Text

Could not start I/O for request %S_BLKIOPTR

Explanation

SQL Server encountered an error while attempting to initiate a read or write request. This error can occur for any of the following reasons:

• A database device is off-line.
• A database device has been removed or renamed.
• A database device is inaccessible for another reason (such as insufficient permissions).

The parameter in the error message refers to an internal structure and does not help determine which database device or file is involved. However, other error messages appearing in the error log just before error 822 usually indicate the device involved.

Action

Examine the availability and condition of the device involved using your standard operating-system procedures, and be sure the device is accessible.

Because error 822 marks the database as "suspect," SQL Server cannot recover the database upon restarting. If you know that the database was marked suspect because the device was unavailable, you can reset the status of the database to allow recovery to continue.

Caution Do not use these procedures if there are other errors in the error log, near the error 822 message, that suggest that the database might have been marked suspect for some other reason.

Reset the suspect status using one of the following procedures:

• Execute the sp_resetstatus procedure as described in Chapter 24, "Additional Problem-solving Techniques." This is the safest method. After you execute sp_resetstatus, restart SQL Server.

Or

• Use the following procedure on the suspect database:
  1. Reset the suspect status, as follows:

     sp_configure 'allow updates', 1
     go
     
     reconfigure with override
     go
     
     use master
     go
     
     begin tran
     go
     
     update sysdatabases
     set status=0 where name='database_name'
     go
     

     When you reset the suspect status using this procedure, all options on the database are reset to their default values. (Using sp_resetstatus leaves these options intact.)

  2. Use sp_dboption to reestablish the options after you complete this procedure. (For additional information, see the Microsoft SQL Server Transact-SQL Reference.)
  3. If only one row is affected by the UPDATE statement, continue with these instructions. If more than one row is affected by the UPDATE statement, roll back the transaction and find out why other rows are being affected.
  4. If the above statements affect only one row, use the following statements to commit the transaction, disable updates to the system tables, issue a checkpoint, and shut down SQL Server:

     commit tran
     go
     
     sp_configure 'allow updates', 0
     go
     
     reconfigure with override
     go
     
     checkpoint
     go
     
     shutdown
     go
     

  5. Restart SQL Server.
  6. If the database is marked suspect again, examine the error log for indications of other errors. If the database is not marked suspect, use sp_dboption to restore database options that might have been reset when you reset the status in the first step of this procedure.

Here are two examples of error 822 and the associated messages from the log. Examining the error messages that occurred just before error 822 usually helps determine the type of problem.

Device missing

In this example, the device C:\SQL60\DATA\DEVICE.DAT (corresponding to virtual device 6) did not exist when SQL Server started, causing access to the device to fail:

kernel: initializing virtual device 6, "c:\sql60\data\device.dat"
kernel: dopen: open "c:\sql60\data\device.dat", No such file or directory
kernel: dinit: failed to open primary device c:\sql60\data\device.dat for 
        vdn 6
kernel: udstartio: vdn 6 has not been set up
server: Error: 822, Severity: 20, State: 3
server: Could not start I/O for request BLKIO .... 

To correct an error like this, be sure that the virtual device appearing in the error message exists, and correct whatever error prevented SQL Server from finding it. For example, it might have been renamed or moved while SQL Server was not running.

If the virtual device no longer exists, you must restore from known clean backups. Merely creating an empty device with the correct name will not solve this problem.

Permission problems

Permission problems can occur if the device is on an NTFS partition.

In this example, the permissions on C:\SQL60\DATA\DEVICE.DAT (virtual device 6) were incorrect, so SQL Server could not access it:

kernel: initializing virtual device 6, "c:\sql60\data\device.dat"
kernel: dopen: open "c:\sql60\data\device.dat", Permission denied
kernel: dinit: failed to open primary device c:\sql60\data\device.dat for 
        vdn 6
kernel: udstartio: vdn 6 has not been set up
server: Error: 822, Severity: 20, State: 3
server: Could not start I/O for request BLKIO .... 

To correct an error like this, change the object ownership for the device to Administrator, and be sure that the account that SQL Server was started under has read and write permissions on the object.