Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

One of our SQL Servers reported the following error recently:

DATE/TIME:  2/25/2013 9:15:14 PM

DESCRIPTION:    No catalog entry found for partition ID 9079262474267394048
     in database 2. The metadata is inconsistent. Run DBCC CHECKDB to check for
     a metadata corruption.

Less than 15 minutes later I connected to the server and ran:

SELECT name
FROM sys.databases
WHERE database_id = 2;

Which returned 'tempdb'. I then ran:

DBCC CHECKDB ('tempdb') WITH NO_INFOMSGS, TABLERESULTS;

Which returned no results, indicating no issues with the database affected.

How could corruption in the database result in the error message above yet DBCC CHECKDB not report the problem? I presume if a page checksum calculation fails, resulting in the page being marked as suspect that any object referencing that page would not be able to be dropped, but I must be wrong.

Once a page is marked 'suspect', how can it be marked not-suspect, or fixed, or reused, or whatever such that DBCC CHECKDB does not report any problem with the page in question?


Edit: 2013-02-27 13:24

Just for fun, I tried to recreate the corruption in TempDB assuming a #temp table was the culprit.

However, since I cannot set the SINGLE_USER option in TempDB, I cannot use DBCC WRITEPAGE to corrupt a page, and therefore I cannot force corruption in TempDB.

Instead of using DBCC WRITEPAGE one could set the database offline and use a hex editor to modify random bytes in the db file. Of course, that does not work either on TempDB since the database engine cannot run with TempDB offline.

If you stop the instance, TempDB is automatically recreated at next startup; hence that won't do the trick either.

If anyone can think of a way to recreate this corruption, I'd be willing to do further research.

In order to test the hypothesis that a corrupted page cannot be fixed by DROP TABLE I created a test database and used the following script to corrupt a page, then attempt to drop the affected table. Result here was the table could not be deleted; I had to RESTORE DATABASE Testdb PAGE = ''... in order to recover the affected page. I assume if I had made a change to some other part of the page in question, perhaps the page could have been corrected with DROP TABLE or perhaps TRUNCATE table.

/* ********************************************* */
/* ********************************************* */
/* DO NOT USE THIS CODE ON A PRODUCTION SYSTEM!! */
/* ********************************************* */
/* ********************************************* */
USE Master;
GO
ALTER DATABASE test SET RECOVERY FULL;
BACKUP DATABASE Test 
    TO DISK = 'Test_db.bak'
    WITH FORMAT
        , INIT
        , NAME = 'Test Database backup'
        , SKIP
        , NOREWIND
        , NOUNLOAD
        , COMPRESSION
        , STATS = 1;
BACKUP LOG Test
    TO DISK = 'Test_log.bak'
    WITH FORMAT
        , INIT
        , NAME = 'Test Log backup'
        , SKIP
        , NOREWIND
        , NOUNLOAD
        , COMPRESSION
        , STATS = 1;
GO
ALTER DATABASE test SET SINGLE_USER;
GO
USE Test;
GO
IF EXISTS (SELECT name FROM sys.key_constraints WHERE name = 'PK_temp') 
    ALTER TABLE temp DROP CONSTRAINT PK_temp;
IF EXISTS (SELECT name FROM sys.default_constraints 
    WHERE name = 'DF_temp_testdata') 
    ALTER TABLE temp DROP CONSTRAINT DF_temp_testdata;
IF EXISTS (SELECT name FROM sys.tables WHERE name = 'temp') 
DROP TABLE temp;
GO
CREATE TABLE temp
(
    tempID INT NOT NULL CONSTRAINT PK_temp PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , testdata uniqueidentifier CONSTRAINT DF_temp_testdata DEFAULT (NEWID())
);
GO

/* insert 10 rows into #temp */
INSERT INTO temp default values;
GO 10 

/* get some necessary parameters */
DECLARE @partitionID bigint;
DECLARE @dbid smallint;
DECLARE @tblid int;
DECLARE @indexid int;
DECLARE @pageid bigint;
DECLARE @offset INT;
DECLARE @fileid INT;

SELECT @dbid = db_id('Test')
    , @tblid = t.object_id
    , @partitionID = p.partition_id
    , @indexid = i.index_id
FROM sys.tables t
    INNER JOIN sys.partitions p ON t.object_id = p.object_id
    INNER JOIN sys.indexes i on t.object_id = i.object_id
WHERE t.name = 'temp';

SELECT TOP(1) @fileid = file_id 
FROM sys.database_files;

SELECT TOP(1) @pageid = allocated_page_page_id 
FROM sys.dm_db_database_page_allocations(@dbid, @tblid, null, @partitionID, 'LIMITED')
WHERE allocation_unit_type = 1;

/* get a random offset into the 8KB page */
SET @offset = FLOOR(rand() * 8192);
SELECT @offset;

/* 0x75 below is the letter 't' */
DBCC WRITEPAGE (@dbid, @fileid, @pageid, @offset, 1, 0x74, 1);


SELECT * FROM temp;

Msg 824, Level 24, State 2, Line 36
SQL Server detected a logical consistency-based I/O error: incorrect checksum
 (expected: 0x298b2ce9; actual: 0x2ecb2ce9). It occurred during a read of page 
 (1:1054) in database ID 7 at offset 0x0000000083c000 in file 'C:\SQLServer
 \MSSQL11.MSSQLSERVER\MSSQL\DATA\Test.mdf'.  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.

At this point you get disconnected from the database engine, so reconnect to continue.

USE Test;
DBCC CHECKDB WITH NO_INFOMSGS, TABLERESULTS;

Corruption is reported here.

DROP TABLE temp;

Msg 824, Level 24, State 2, Line 36
SQL Server detected a logical consistency-based I/O error: incorrect checksum
 (expected: 0x298b2ce9; actual: 0x2ecb2ce9). It occurred during a read of page 
 (1:1054) in database ID 7 at offset 0x0000000083c000 in file 'C:\SQLServer
 \MSSQL11.MSSQLSERVER\MSSQL\DATA\Test.mdf'.  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.

Corruption is reported here, DROP TABLE fails.

/* assuming ENTERPRISE or DEVELOPER edition of SQL Server,
    I can use PAGE='' to restore a single page from backup */
USE Master;
RESTORE DATABASE Test PAGE = '1:1054' FROM DISK = 'Test_db.bak'; 
BACKUP LOG Test TO DISK = 'Test_log_1.bak';

RESTORE LOG Test FROM DISK = 'Test_log.bak';
RESTORE LOG Test FROM DISK = 'Test_log_1.bak';

Edit #2, to add the @@VERSION info requested.

SELECT @@VERSION;

Returns:

Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) 
    Oct 19 2012 13:38:57 
    Copyright (c) Microsoft Corporation
    Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> 
        (Build 9200: )

I know this is the Evaluation Edition, we have keys for the Enterprise Edition, and will be doing an Edition Upgrade soon.

share|improve this question
1  
Perhaps it was a #temp table with some kind of weird issue, but by the time you ran your check, it had been dropped / destroyed? Not sure I'm convinced the error message means that a page was marked suspect. –  Aaron Bertrand Feb 27 '13 at 17:27
1  
There's also this, but you'd think it would have been fixed by 2012: sqlservercentral.com/Forums/Topic770808-149-1.aspx –  Stu Feb 27 '13 at 17:28
2  
I still think you're making a large leap in assuming that the "corruption" in tempdb (which had a completely different error message) is the same as the corruption you reproduced in your user database. I don't think it is. The message you got for problematic metadata in tempdb is NOT the same as Msg 824. –  Aaron Bertrand Feb 27 '13 at 20:40
2  
Also keep in mind that a #temp table might have different rules about durability than a user table. Even if a page is marked suspect (and I'm not sure that's the case, again leaps and such), once the owning session goes out of scope, it might be possible that the #temp table can be destroyed anyway. Is this something that happened ONCE, or has it happened repeatedly? What is @@VERSION? –  Aaron Bertrand Feb 27 '13 at 20:47
2  
FYI -T 3609 will preserve tempdb at start (undocumented but already known) –  Remus Rusanu Feb 27 '13 at 22:17

2 Answers 2

up vote 4 down vote accepted

Yes, but specifically, an catalog error can't be checked in TempDB. You should recycle SQL Server if possible to fix this. Per MSDN:

"Running DBCC CHECKCATALOG against tempdb does not perform any checks. This is because, for performance reasons, database snapshots are not available on tempdb. This means that the required transactional consistency cannot be obtained. Recycle the server to resolve any tempdb metadata issues."

MSDB article is here: http://msdn.microsoft.com/en-us/library/ms186720.aspx

share|improve this answer
    
Thanks, Kate! Did you join StackExchange just to answer my question? Hard to imagine, but thanks either way! –  Max Vernon Mar 1 '13 at 15:15
1  
I'm a long time lurker :) Normally all the questions are resolved when I read them. I had a similar experience with this so I figured it was time to chime in officially! –  Cate Donoghue Mar 1 '13 at 16:40

Running CHECKDB against tempdb is not the same as running it against a user database.

From MSDN:

Running DBCC CHECKDB against tempdb does not perform any allocation or catalog checks and must acquire shared table locks to perform table checks. This is because, for performance reasons, database snapshots are not available on tempdb. This means that the required transactional consistency cannot be obtained.

share|improve this answer
1  
Thanks for finding that, Jon. The question this raises in my mind is does DBCC CHECKDB (DB_ID('tempdb')) find all corruption? Could my tempdb in question still have corruption and should I force tempdb to be rebuilt by restarting the instance? –  Max Vernon Feb 27 '13 at 19:38
2  
@Max: I assume it does the best job it can. Because of what tempdb is and does, there are certain limitations (as shown). Potentially, yes, your tempdb could still contain corruption. I don't know what the cause or solution is here, though. It's possible you have an I/O subsystem problem (check all your SQL and Windows event logs) in which case recreating tempdb only masks the problem for a while, or it could be a rare SQL Server bug that will never resurface. I'm really not an expert in this area. Maybe someone else can chime in here. –  Jon Seigel Feb 27 '13 at 20:18
    
At this risk of seeming pedantic, I've marked Cate's answer as correct even though your answer is essentially the same. I chose Cate's answer since it explicitly says to "Recycle the server to resolve any tempdb metadata issues". –  Max Vernon Mar 1 '13 at 18:05
    
@Jon and Max, I have been receiving the same error message (608 I believe) on tempdb, also in SQL Server 2012 sp1. The comment about IO subsystem issues is interesting... But my (possible) IO issues are not on the tempdb drive. See my msdn forum post here for details: social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/… –  sqlbattsman May 9 '13 at 22:40
    
@sqlbattsman: Interesting. I would see if replacing the drive fixes the issue. I'm really not sure how it could be connected to issues in tempdb, though. If fixing the hardware doesn't solve the issue, consider opening a support ticket with Microsoft. –  Jon Seigel May 9 '13 at 23:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.