Share via

SQL Server Troubleshooting: DBCC CHECKDB Failed or Could not read and latch page with latch type SH

  • Article

Introduction

Normally with this sort of error, we will follow the below procedure:

ALTER DATABASE  <database  name> SET EMERGENCY
 
GO
 
DBCC CHECKDB WITH  ALL_ERRORMSGS

Based on error we will try to Repair the Database. If this fails we will go ahead and restore with the latest full backup or with a strong backup strategy we are following in our environments.

There are few chances we need to look at the space on the disk drives too.

Error

The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.

Resolution

Verify the space on the Disk and check the growth of the DB on the drive.

http://support.microsoft.com/kb/926070

CAUSE

In SQL Server, DBCC commands use internal read-only database snapshots. These database snapshots are created on the same drive where the corresponding database data files are located. Database snapshots grow in proportion to the amount of changed data in the database against which the DBCC commands run. If transactional activity continues on this database, the database snapshots that are created by DBCC commands may experience disk space issues. Because the database snapshot files and the actual data files reside on the same disk drive, both sets of files compete for disk space. In this case, application transactions or user transactions are given preference. The internal database snapshot used by DBCC is marked as suspect. Therefore, the DBCC commands experience errors and cannot finish.

Disk Space is one reason why writes to the internal database snapshot may fail. Other reasons such as OS error codes 1450 and 665 can also contribute to similar issues and render the internal database snapshot to a suspect state.