DBCC CHECK commands encounter problems and reports errors
Microsoft SQL Server support team received several support incidents from SQL Server Customers who observed a strange issue when executing DBCC CHECK family of commands against their production databases. We investigated this problem and found a resolution to this issue.
Here are some of the identifying characteristics of this problem we noticed among all these customers:
· These issues started happening for these customers in the past few weeks
o There were no updates applied for SQL Server just before the problem started
o There were no updates applied to Windows OS just before the problem started
· All of these databases were fine and reported no issues until this point
· When you set the database to SINGLE_USER access mode and execute these DBCC CHECK commands, then no errors are reported and DBCC CHECK comes back clean
· When you set the database to MULTI_USER access mode and execute these DBCC CHECK commands, then various errors are reported in the DBCC CHECK output
· When you set the database to MULTI_USER access mode and execute these DBCC CHECK commands with TABLOCK option, then no errors are reported and DBCC CHECK comes back clean
o The above observation clearly tells us that there is some problem on the system when we use the internal snapshot for DBCC CHECK. More on this in BOL topic “DBCC CHECKDB (Transact-SQL)” section “Internal Database Snapshot”.
· The SQL Server Error logs and Windows Application log show the following error sequences. These may vary for different executions and various parameters involved, but will give you an idea of what to look for and how to correlate this back to the problem you are encountering.
Snippet #1:
2009-12-02 00:33:28.41 spid66 A read of the file 'C:Program FilesMicrosoft SQL ServerMSSQL.SQL2008MSSQLDATAAdventureWorks.mdf:MSSQL_DBCC8' at offset 0x000000176e8000 succeeded after failing 1 time(s) with error: 38(Reached the end of the file.). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
2009-12-02 00:33:38.83 spid66 Error: 823, Severity: 24, State: 2.
2009-12-02 00:33:38.83 spid66 The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0x0000001554c000 in file 'C:Program FilesMicrosoft SQL ServerMSSQL.SQL2008MSSQLDATAAdventureWorks.mdf:MSSQL_DBCC8'. 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.
2009-12-02 00:33:38.92 spid66 Error: 8966, Severity: 16, State: 2.
2009-12-02 00:33:38.92 spid66 Unable to read and latch page (1:7068) with latch type SH. 38(Reached the end of the file.) failed.
2009-12-02 00:33:38.93 spid66 DBCC CHECKDB (ADVENTUREWORKS) WITH physical_only executed by DomainUser found 8 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 20 seconds.
Snippet #2:
2009-12-02 15:46:33.59 spid61 A read of the file 'C:Program FilesMicrosoft SQL ServerMSSQL.SQL2008MSSQLDATAAdventureWorks.mdf:MSSQL_DBCC8' at offset 0x000000176ea000 succeeded after failing 1 time(s) with error: 38(Reached the end of the file.). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
2009-12-02 15:46:42.90 spid61 Error: 824, Severity: 24, State: 2.
2009-12-02 15:46:42.90 spid61 SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:43686; actual 0:0). It occurred during a read of page (1:43686) in database ID 33 at offset 0x0000001554c000 in file 'C:Program FilesMicrosoft SQL ServerMSSQL.SQL2008MSSQLDATAAdventureWorks.mdf:MSSQL_DBCC8'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe 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.
2009-12-02 15:46:42.92 spid61 Error: 8966, Severity: 16, State: 2.
2009-12-02 15:46:42.92 spid61 Unable to read and latch page (1:47928) with latch type SH. 38(Reached the end of the file.) failed.
2009-12-02 15:46:42.92 spid61 DBCC CHECKDB (ADVENTUREWORKS) WITH physical_only executed by DomainUser found 8 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 19 seconds.
Snippet #3:
2009-12-02 15:52:47.26 spid18s The operating system returned error 1784(The supplied user buffer is not valid for the requested operation.) to SQL Server during a write at offset 0x000000176c8000 in file ‘C:Program FilesMicrosoft SQL ServerMSSQL.SQL2008MSSQLDATAAdventureWorks.mdf:MSSQL_DBCC8’. 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.
2009-12-02 15:52:47.26 spid18s Error: 17053, Severity: 16, State: 1.
2009-12-02 15:52:47.26 spid18s C:Program FilesMicrosoft SQL ServerMSSQL.SQL2008MSSQLDATAAdventureWorks.mdf:MSSQL_DBCC8: Operating system error 1784(The supplied user buffer is not valid for the requested operation.) encountered.
2009-12-02 15:52:47.32 spid58 DBCC CHECKDB (ADVENTUREWORKS) WITH no_infomsgs executed by DomainUser found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 3 seconds.
2009-12-02 15:52:47.37 spid58 Error: 926, Severity: 21, State: 6.
2009-12-02 15:52:47.37 spid58 Database ‘ADVENTUREWORKS’ cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
· The common theme here is that the file name that encounters the 823 and 824 error messages represents the alternate ntfs stream as indicated by the format AdventureWorks.mdf:MSSQL_DBCC8
· Another important point to note is that read-retry succeeds in some instances and fails in some instances. More on read-retry technique @ MSSQLSERVER_825
· We confirmed that these customers have the fix for a known issue we had seen several years back:
o https://support.microsoft.com/default.aspx?scid=kb;EN-US;909003
o https://support.microsoft.com/default.aspx?scid=kb;EN-US;909532
· Next we started looking for any new software that installs filter drivers that could contribute to this issue. We observed that just a few days before this DBCC CHECK problem started, all these customers had the following entry in their Windows Application logs:
12/1/2009,06:45:59 PM,MsiInstaller,Information,None,11707,NT AUTHORITYSYSTEM,<Machine Name>,Product: Diskeeper 2010 Server -- Installation operation completed successfully.
· Then we looked at the output from the MPS reports to see if there are any filter drivers installed by this product. We found one driver which seemed related to this product.
Module[113] [C:WINDOWSSYSTEM32DRIVERSDKRTWRT.SYS]
Company Name: Diskeeper Corporation
File Description: Diskeeper IntelliWrite Mini-Filter Driver
Product Version: (1.0:4.0)
File Version: (1.0:4.0)
File Size (bytes): 51120
File Date: Wed Oct 21 00:04:36 2009
Module TimeDateStamp = 0x4adebff8 – Wed Oct 21 04:02:00 2009
Module Checksum = 0x0001af4b
Module SizeOfImage = 0x00010000
Module Pointer to PDB = [d:workspacesfilterdriversrightwrite_rtmsourcessourcesdkrtwrtobjfre_wnet_amd64amd64DKRtWrt.pdb]
Module PDB Guid = {4DCE00A2-A7B2-4A87-B85D-8F02C91D7153}
Module PDB Age = 0x1
So armed with this information, we attempted a Bing search and landed into this report which confirmed our suspicions:
https://www.diskeeper.com/blog/post/2009/12/28/New-Diskeeper-2010-update-%28140898%29.aspx
So if you are using Diskeeper facilities on your SQL Server, make sure to follow the recommendations from Diskeeper for this issue as specified in the above bulletin.
If we find any other information regarding this issue, we will update this post.
Suresh B. Kandoth
Senior Escalation Engineer
Microsoft SQL Server
Microsoft Corporation