CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

DBCC CHECK commands encounter problems and reports errors

DBCC CHECK commands encounter problems and reports errors

Rate This
  • Comments 3

 

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 Files\Microsoft SQL Server\MSSQL.SQL2008\MSSQL\DATA\AdventureWorks.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 Files\Microsoft SQL Server\MSSQL.SQL2008\MSSQL\DATA\AdventureWorks.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 Domain\User 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 Files\Microsoft SQL Server\MSSQL.SQL2008\MSSQL\DATA\AdventureWorks.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 Files\Microsoft SQL Server\MSSQL.SQL2008\MSSQL\DATA\AdventureWorks.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 Domain\User 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 Files\Microsoft SQL Server\MSSQL.SQL2008\MSSQL\DATA\AdventureWorks.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 Files\Microsoft SQL Server\MSSQL.SQL2008\MSSQL\DATA\AdventureWorks.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 Domain\User 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    http://support.microsoft.com/default.aspx?scid=kb;EN-US;909003

o    http://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 AUTHORITY\SYSTEM,<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:\WINDOWS\SYSTEM32\DRIVERS\DKRTWRT.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:\workspaces\filterdrivers\rightwrite_rtm\sources\sources\dkrtwrt\objfre_wnet_amd64\amd64\DKRtWrt.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:

http://www.diskeeper.com/blog/post/2009/12/10/IntelliWrite-and-SQL-DBCC-false-positives-Technical-Bulletin.aspx

http://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

 

Leave a Comment
  • Please add 6 and 4 and type the answer here:
  • Post
  • Can a filter driver with a bug and or bugs in it corrupt data and files passing through it or are they just passive ?

    Is it best to keep the number of filter drivers on a system to as few as possible ?

  • I can produce this when installing, using the following command line [parameters]:

    setup.exe /ACTION=Install /SECURITYMODE=SQL /SAPWD="Sq1S#rv$r"

    /INSTANCENAME="SQLEXPRESS" /FEATURES=SQLEngine,Replication,SDK

    /SQLSVCACCOUNT="NT AUTHORITY\NETWORK SERVICE"

    /SQLSYSADMINACCOUNTS="WIN-98N32E9H1DD\c"

    /IAcceptSQLServerLicenseTerms=TRUE

  • This type of sql error can be eliminated by using trustful sql recovery software which recover corrupt sql database without any damage. Go through this for more information:- http://www.sqlrepairtool.org

Page 1 of 1 (3 items)