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.
-T 3609will preserve tempdb at start (undocumented but already known)