FIX: A scan of data that uses the unordered page supplier reports error 605, error 625, error 644, and error 823 in SQL Server 2000 Service Pack 3 (826436)

SYMPTOMS

When the unordered page supplier is used to scan for data, Microsoft SQL Server may log false corruption errors such as error 605, error 625, error 644, and error 823.

When you run a DBCC CHECKDB statement on a database that was reported to be corrupted, you may see that DBCC CHECKDB does not report any database corruption. In such situations, error messages that are similar to the following may be logged in the SQL Server error log:

2003-07-24 16:52:37.67 spid63 Error: 605, Severity: 21, State: 1
2003-07-24 16:52:37.67 spid63 Attempt to fetch logical page (1:7040966) in database 'PUBS' belongs to object 'Authors', not to object 'Titles'..

2003-07-09 14:31:35.92 spid66 Error: 823, Severity: 24, State: 2
2003-07-09 14:31:35.92 spid66 I/O error (bad page ID) detected during read at offset 0x00000016774000 in file 'h:\sql\MSSQL\data\tempdb.mdf'..

MORE INFORMATION

The unordered page supplier is used to scan for data in heaps during the building of an index or a statistics update. (A heap is a table that does not have a clustered index). In some rare circumstances, while SQL Server is scanning the Index Allocation Map (IAM) chain to perform an unordered page traversal, SQL Server may use a NOLOCK locking hint and a READ UNCOMMITTED isolation level optimization. Use of a NO LOCK locking hint and a READ UNCOMMITTED isolation level can affect the pages that are allocated in the IAM when the read was posted. However, if a second transaction deallocates the page before the data on the page is actually used, the behavior can become undefined.

The undefined behavior occurs because the actual in-memory image of the page may be reused after the page is deallocated. Therefore, in addition to other types of problems such as assertions, you will see corruption errors such as error 605, or error 644 logged in the SQL Server error log.

The following is a sample call stack from a computer that is running SQL Server that encountered error 605. The error is triggered when you use -y605 as a startup parameter or when you use -y605 with a dump trigger action.

sqlservr!stackTrace+0x51
sqlservr!ex_raise2+0x160 
sqlservr!ex_raise+0x5c 
sqlservr!getpagecheck+0x232 
sqlservr!SDES::GetPage+0x124 
sqlservr!UnorderedPageSupplier::GetNextPage+0x1a7 
sqlservr!SDES::GetBiDi+0x19c 
sqlservr!RowsetSS::FetchNextRow+0x9b 
sqlservr!CQScanRowset::GetRowWithPrefetch+0x40 
sqlservr!CQScanTableScan::GetRow+0x5f 
sqlservr!CQScanSort::BuildSortTable+0x134 
sqlservr!CQScanSort::Open+0x32 
sqlservr!CQScan::Open+0x1c 
sqlservr!CQScanStreamAggregate::Open+0xe 
sqlservr!CQueryScan::Startup+0x10d 
sqlservr!CStmtQuery::ErsqExecuteQuery+0x26b 
sqlservr!CStmtSelect::XretExecute+0x229 
sqlservr!CMsqlExecContext::ExecuteStmts+0x3b9 
sqlservr!CMsqlExecContext::Execute+0x1b6 
sqlservr!CSQLSource::Execute+0x357 
sqlservr!ExecuteSql+0x284 
sqlservr!ExecUpdStatsStmt+0x681 
sqlservr!FUpsBuildStats+0x361 
sqlservr!CreateStatistics+0x1cf 
sqlservr!E_INDEXDEF::Execute+0x80f 
sqlservr!CreateStatisticsDriver+0x67 
sqlservr!updatestats+0x723 
sqlservr!COptContext::FInstantiateGuessedStats+0x64a

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

824684 Description of the standard terminology that is used to describe Microsoft software updates

FIX: A scan of data that uses the unordered page supplier reports error 605, error 625, error 644, and error 823 in SQL Server 2000 Service Pack 3 (826436)

SYMPTOMS

RESOLUTION

Service pack information

STATUS

MORE INFORMATION