FIX: Attentions During Nonlogged Allocations in Tempdb Can Result in Errors 8906 and 8946 (278363)

SYMPTOMS

Attentions that are raised by the client while the server is executing commands, requiring nonlogged allocations in tempdb such as worktables for cursors, can result in the following errors when you run CHECKDB on the tempdb database:

Server: Msg 8906, Level 16, State 1, Line 1
Page (1:253) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL'.

Server: Msg 8946, Level 16, State 1, Line 1
Table Corrupt: Allocation page (1:253) has invalid IAM_PAGE page header values. Type is 1. Check type, object ID and page ID on the page.

WORKAROUND

Reduce the frequency of attentions to the server during tempdb operations. You may need to troubleshoot blocking problems or increase an application query timeout. To avoid this problem, you can also reduce or eliminate the usage of queries that require tempdb worktables, such as cursors, ORDER BY, and so on.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

274799 INF: How to Obtain Service Pack 3 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.

MORE INFORMATION

Typically, no errors occur during the processing of the attention. A problem is only noticed when running DBCC CHECKDB on tempdb. Be careful when you run DBCC CHECKDB against tempdb because it can report "false" errors if tempdb activity is taking place at the same time that the CHECKDB is run. You must ensure that no tempdb activity is taking place when running CHECKDB to see if this particular problem is being encountered.

Because of these errors, the size of tempdb may unexpectedly grow, and users may not be able to reclaim unused tempdb space over time. Depending on the configured size of tempdb and the available disk space, the user may unexpectedly encounter Error 1105 on tempdb.