Home Dashboard Directory Help
Search

Database Corruption from model database with large table by Jonathan Kehayias


Status: 

Closed
 as Fixed Help for as Fixed


6
0
Sign in
to vote
Type: Bug
ID: 569843
Opened: 6/22/2010 9:27:26 PM
Access Restriction: Public
0
Workaround(s)
view
2
User(s) can reproduce this bug

Description

If you create a significantly large table in the model database and shrink the database and log, all databases created on the server will fail DBCC CHECKDB with corruption similar to:

DBCC results for 'Test'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
<truncated entries>
Msg 8909, Level 16, State 1, Line 2
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:1022480) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Msg 8909, Level 16, State 1, Line 2
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:1022480) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Msg 8909, Level 16, State 1, Line 2
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:1022483) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
CHECKDB found 0 allocation errors and 3 consistency errors not associated with any single object.
DBCC results for 'sys.sysrscols'.
There are 634 rows in 7 pages for object "sys.sysrscols".
<truncated entries>
CHECKDB found 0 allocation errors and 3 consistency errors in database 'Test'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Test).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

In addition if you attempt to do a SELECT from the object in a user database you will get:

Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:691341; actual 0:0). It occurred during a read of page (1:691341) in database ID 25 at offset 0x0000015191a000 in file 'D:\SQLData\MSSQL10.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.

If you run DBCC CHECKDB in the model database it will succeed without errors and if you issue a SELECT against the table in the model database it will return results without problems.
Details
Sign in to post a comment.
Posted by Microsoft on 7/26/2010 at 9:31 AM
Thank you for the feedback and repro script. It did repro and we have identified the problem. It will be fixed for the next release. If this is affecting your normal operations, please contact customer support for a hotfix for this to be issued for your specific version of product.

Thank you very much.
Posted by Robert L Davis on 6/22/2010 at 10:55 PM
Repro'd for me on Win7 x86 / SQL 2008 SP1 Dev Ed.

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) Mar 29 2009 10:27:29 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition on Windows NT 6.1 <X86> (Build 7600: )

Error: Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: invalid protection option. It occurred during a read of page (1:739152) in database ID 8 at offset 0x00000168ea0000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL10.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.

Running DBCC CheckTable() for the large table in the model database worked, but running it in the new user database failed with the following error:

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

Repro code sent to me by OP:

USE [master]
GO
ALTER DATABASE model SET RECOVERY FULL
GO
USE [model]
GO
-- Suppress data loading messages
SET NOCOUNT ON

-- Create Sample Data using a Table Varable
SELECT TOP 2147483647 IDENTITY(INT,0,1) AS N
INTO Numbers
FROM sysobjects a, sysobjects b, sysobjects c, sysobjects d, sysobjects e

-- Create a Primary Key and Clustered Index to control data order
ALTER TABLE dbo.Numbers ADD CONSTRAINT
PK_Numbers PRIMARY KEY CLUSTERED (N)
USE master;
GO
ALTER DATABASE [model] SET RECOVERY SIMPLE
GO
USE [model]
GO
DBCC SHRINKFILE (N'modellog' , 0, TRUNCATEONLY)
GO
USE [model]
GO
DBCC SHRINKFILE (N'modeldev' , 6144)
GO
USE [master];
GO
CREATE DATABASE Test
GO
USE [Test]
GO
SELECT * FROM Numbers
GO
Sign in to post a workaround.