The MS SQL Server engine uses lightweight synchronization primitives, called latches, to ensure the consistency of in-memory structures, data pages, and indexes. The two common kinds of latches are PAGELATCH and PAGEIOLATCH. The PAGELATCH is present in the buffer pool, which protects the pages in it. And PAGEIOLATCH is on the disk that helps protect pages that are not loaded yet into the buffer pool.
In SQL Server, there is no direct access path to disk. At any time when you call a page, the server reads it from the buffer pool first. So, the page goes to the buffer pool which creates a page latch. If the server fails to read the page latch due to latch contention or any other reasons, it may throw an error looks similar to this:
This error usually occurs if there is corruption in the buffer pool, pages & their indexes, data pages, and meta pages (PFS/GAM), indexes (B-tree, Hash-tree), or latch pages of any type, like SH, EX, DT, in the MS SQL database. Here, we will learn the troubleshooting methods to resolve this error.
Reasons for SQL Server Error 8966 - Unable to Read and Latch Page
You can experience the “Msg 8966, Level 16, State 1, Line 1 Could not read and latch page (1:1681) with latch type SH. sysindexes failed” error due to one of the following reasons:
- I/O or CRC issues in your system’s hard disk
- Corruption in PFS_PAGE page header values
- Damaged or corrupted indexes in NDF/MDF file
- Corruption in MS SQL database file
- The server fails to get the required latch in time due to high contention when multiple threads are trying to access the same page simultaneously
Methods to Resolve Error 8966 – Unable to Read and Latch Page
To resolve this SQL server error, follow the methods mentioned below.
Method 1 - Remove the Latch Contention
SQL Server uses a latch to protect buffer pool pages and creates a thread for each session. At times, the latch contention occurs. For instance, when multiple users attempt to update a single page at the same time. Due to this, multiple threads are created for each connection session, resulting in wait times. To check whether the error is the result of latch contention, you can run the SQL Server DMVs (System Dynamic Management Views). It helps you determine the specific type of latch behind your error. To check this, you can open system dynamic management views by running the below query:
SELECT wait_type, wait_time_ms
FROM sys.dm_os_wait_stats;
Also, you can run the DMVs from your system to check latch wait times and CPU utilization to troubleshoot the issue. For this,
- Open your system’s Run app and type perfmon and click OK.
- In the Performance Monitor window, click Performance Monitor, and click on the + sign.
- Under the Add counter window, search for SQLServer:BufferManager, SQLServer:Latches, and then create a report.
Check the report. Once you have found the exact latch issue that is causing the error, then resolve latch contention by the following ways:
- Use hash partitioning
- Use a non-sequential key/index in place of a sequential key/index. For example, in case you want to distribute values across the index key range, use a column of the table itself.
Method 2 - Check and Fix SQL Database Corruption
The error 8966 – Unable to Read and Latch Page can occur due to corruption in pages, indexes, or other elements in the MS SQL database. To check this, run the DBCC CHECKDB command or execute the SELECT statement as given below:
This command will return a grid with a row of each corrupted page. It includes file ID and Page ID. Once you know the specific pages in database that are corrupted, you can perform a page-level backup restore to fix the page corruption in SQL Server. To do so, run the below command:
PAGE = '
FROM
WITH NORECOVERY
Otherwise, you can restore the backup file to restore the corrupt MDF/NDF file.
If the backup is not readable, then you can use the DBCC CHECKDB command to repair the database. It can help you resolve page-level corruption issues in database. The command can be used with different repair options. Before running any of the repair options, check the database-level permissions.
Steps to use DBCC CHECKDB command to repair MS SQL database:
- First, change the corrupted database to SINGLE_USER mode.
- Next, click on Next Query in the SSMS. Then, run the DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS option to repair the database.
DBCC CHECKDB (N ’department’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO
- After repairing the database, you need to change the database mode from SINGLE_USER to MULTI_USER. For this, run the below command:
The DBCC CHECKDB command can help you resolve the "Error 8966 - Unable to read and latch page" if it occurs due to corruption in pages in the SQL database. But it may reallocate or delete the corrupted pages while restoring, which can cause data loss. As it does not guarantee the complete database recovery, you can repair SQL database by using Stellar Repair for MS SQL.
Why this SQL repair tool is better?
- Stellar Repair for MS SQL can help you repair NDF/MDF files, including pages, indexes, and stored procedures, in a short time.
- Repairs NDF/MDF files created in any server version, of any size, or in any state.
- Restores corrupt pages without any data loss.
- Supports recovery of MS SQL Server ROW compressed data and PAGE compressed data.
- Allows you to save the repaired database in a wide range of file formats, like CSV, HTML, XLS, and to a new database and live database.
- Provides an option to preview the repaired database before saving. From the preview window, you can even use the find option to select specific items.
- Helps you resolve page-level corruption issues, like 8966, 8946, etc., with complete integrity.
Must Read: SQL Database Corruption and how Stellar restored transaction seamlessly. Case Study Link
Conclusion
The causes of SQL Server error 8966 - Unable to read and latch page are corruption in pages and latch contention. To case of corruption in pages, you can use the PAGE RESTORE or DBCC CHECKDB command in MS SQL Server to fix the issue. But if these commands fail to bring the desired outcome, then using Stellar Repair for MS SQL is the apt solution. It can quickly repair highly corrupted SQL database files and restore all the data, including Buffer pool, hash indexes and pages.
Monika Dadool is a Senior Content Writer at Stellar with over 5 years of experience in technical writing.
5 min read
