· 8 years ago · Apr 12, 2018, 07:40 AM
1DATE/TIME: 2/25/2013 9:15:14 PM
2
3DESCRIPTION: No catalog entry found for partition ID 9079262474267394048
4 in database 2. The metadata is inconsistent. Run DBCC CHECKDB to check for
5 a metadata corruption.
6
7SELECT name
8FROM sys.databases
9WHERE database_id = 2;
10
11DBCC CHECKDB ('tempdb') WITH NO_INFOMSGS, TABLERESULTS;
12
13/* ********************************************* */
14/* ********************************************* */
15/* DO NOT USE THIS CODE ON A PRODUCTION SYSTEM!! */
16/* ********************************************* */
17/* ********************************************* */
18USE Master;
19GO
20ALTER DATABASE test SET RECOVERY FULL;
21BACKUP DATABASE Test
22 TO DISK = 'Test_db.bak'
23 WITH FORMAT
24 , INIT
25 , NAME = 'Test Database backup'
26 , SKIP
27 , NOREWIND
28 , NOUNLOAD
29 , COMPRESSION
30 , STATS = 1;
31BACKUP LOG Test
32 TO DISK = 'Test_log.bak'
33 WITH FORMAT
34 , INIT
35 , NAME = 'Test Log backup'
36 , SKIP
37 , NOREWIND
38 , NOUNLOAD
39 , COMPRESSION
40 , STATS = 1;
41GO
42ALTER DATABASE test SET SINGLE_USER;
43GO
44USE Test;
45GO
46IF EXISTS (SELECT name FROM sys.key_constraints WHERE name = 'PK_temp')
47 ALTER TABLE temp DROP CONSTRAINT PK_temp;
48IF EXISTS (SELECT name FROM sys.default_constraints
49 WHERE name = 'DF_temp_testdata')
50 ALTER TABLE temp DROP CONSTRAINT DF_temp_testdata;
51IF EXISTS (SELECT name FROM sys.tables WHERE name = 'temp')
52DROP TABLE temp;
53GO
54CREATE TABLE temp
55(
56 tempID INT NOT NULL CONSTRAINT PK_temp PRIMARY KEY CLUSTERED IDENTITY(1,1)
57 , testdata uniqueidentifier CONSTRAINT DF_temp_testdata DEFAULT (NEWID())
58);
59GO
60
61/* insert 10 rows into #temp */
62INSERT INTO temp default values;
63GO 10
64
65/* get some necessary parameters */
66DECLARE @partitionID bigint;
67DECLARE @dbid smallint;
68DECLARE @tblid int;
69DECLARE @indexid int;
70DECLARE @pageid bigint;
71DECLARE @offset INT;
72DECLARE @fileid INT;
73
74SELECT @dbid = db_id('Test')
75 , @tblid = t.object_id
76 , @partitionID = p.partition_id
77 , @indexid = i.index_id
78FROM sys.tables t
79 INNER JOIN sys.partitions p ON t.object_id = p.object_id
80 INNER JOIN sys.indexes i on t.object_id = i.object_id
81WHERE t.name = 'temp';
82
83SELECT TOP(1) @fileid = file_id
84FROM sys.database_files;
85
86SELECT TOP(1) @pageid = allocated_page_page_id
87FROM sys.dm_db_database_page_allocations(@dbid, @tblid, null, @partitionID, 'LIMITED')
88WHERE allocation_unit_type = 1;
89
90/* get a random offset into the 8KB page */
91SET @offset = FLOOR(rand() * 8192);
92SELECT @offset;
93
94/* 0x75 below is the letter 't' */
95DBCC WRITEPAGE (@dbid, @fileid, @pageid, @offset, 1, 0x74, 1);
96
97
98SELECT * FROM temp;
99
100Msg 824, Level 24, State 2, Line 36
101SQL Server detected a logical consistency-based I/O error: incorrect checksum
102 (expected: 0x298b2ce9; actual: 0x2ecb2ce9). It occurred during a read of page
103 (1:1054) in database ID 7 at offset 0x0000000083c000 in file 'C:SQLServer
104 MSSQL11.MSSQLSERVERMSSQLDATATest.mdf'. Additional messages in the SQL
105 Server error log or system event log may provide more detail. This is a
106 severe error condition that threatens database integrity and must be
107 corrected immediately. Complete a full database consistency check
108 (DBCC CHECKDB). This error can be caused by many factors; for more
109 information, see SQL Server Books Online.
110
111USE Test;
112DBCC CHECKDB WITH NO_INFOMSGS, TABLERESULTS;
113
114DROP TABLE temp;
115
116Msg 824, Level 24, State 2, Line 36
117SQL Server detected a logical consistency-based I/O error: incorrect checksum
118 (expected: 0x298b2ce9; actual: 0x2ecb2ce9). It occurred during a read of page
119 (1:1054) in database ID 7 at offset 0x0000000083c000 in file 'C:SQLServer
120 MSSQL11.MSSQLSERVERMSSQLDATATest.mdf'. Additional messages in the SQL
121 Server error log or system event log may provide more detail. This is a
122 severe error condition that threatens database integrity and must be
123 corrected immediately. Complete a full database consistency check
124 (DBCC CHECKDB). This error can be caused by many factors; for more
125 information, see SQL Server Books Online.
126
127/* assuming ENTERPRISE or DEVELOPER edition of SQL Server,
128 I can use PAGE='' to restore a single page from backup */
129USE Master;
130RESTORE DATABASE Test PAGE = '1:1054' FROM DISK = 'Test_db.bak';
131BACKUP LOG Test TO DISK = 'Test_log_1.bak';
132
133RESTORE LOG Test FROM DISK = 'Test_log.bak';
134RESTORE LOG Test FROM DISK = 'Test_log_1.bak';
135
136SELECT @@VERSION;
137
138Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
139 Oct 19 2012 13:38:57
140 Copyright (c) Microsoft Corporation
141 Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64>
142 (Build 9200: )