WARNING: The following post involves intentionally doing damage to a *system* database. The usual caveats about having good backups, not attempting to do this on an important system, etc… apply here. You may very well need to remove and reinstall SQL Server if you attempt this. In short – “We’re professionals. Do not try this at home.”
Today’s lesson is about equality. Treat all databases with equal kindness. For example, I see a lot of people not doing basic maintenance like regular backups and CHECKDB on their system databases. Maybe they think that since those databases don’t change much, there’s no reason to protect them the way that they protect user data.
I am here today to dispel that notion. If you’ve attended one of my sessions on database corruption, you’ll remember that I state that database corruption is just a fact of life. It happens, and it can happen when and where you least expect it. Example: in the [model] database.
The [model] database is essentially SQL Server’s template. When a new database is created, first, a copy of [model] is made. Then, any further configurations or tweaks that the user specified in either the database creation dialog boxes, or the CREATE DATABASE command, are applied to the new database. The advantage of this is that anything you want to apply to any newly created databases, you can apply to the [model] database. Any objects you want in all new databases, any default file settings like size and growth, or something SIMPLE like the database’s recovery model. (See what I did there? :-))
Let me repeat that real quick – *anything* that exists in [model] will be copied to new databases.
Including corrupted pages.
Sooo… Let’s do one! :-)
First, take a backup of [model] and set it aside, just in case. Then we create an object we can copy to new databases.
/* Create a table that can get propagated to new databases. */ CREATE TABLE Trash (datacol CHAR(8000) NOT NULL); /* if you're gonna go, go big. */ GO
/* Drop in a row. */ INSERT INTO Trash(datacol) SELECT REPLICATE('a',7500); GO
Now that we have an object, we need to corrupt it. I chose to use the method described here. You’ll need the ID of the page you want to corrupt, which you can get with DBCC IND:
/* Get the page ID of the one lone page in the Trash table. */
DBCC IND('model','Trash',0); GO
Now, let’s assume we’re not checking [model] for corruption, and so it goes undetected. What happens when I create a new database?
/* Create new database and check for corruption. */
CREATE DATABASE TestMe; GO
DBCC CHECKDB(TestMe) WITH NO_INFOMSGS; GO
Msg 8939, Level 16, State 98, Line 103 Table error: Object ID 581577110, index ID 0, partition ID 72057594039107584, alloc unit ID 72057594043695104 (type In-row data), page (1:312). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -4. Msg 8928, Level 16, State 1, Line 103 Object ID 581577110, index ID 0, partition ID 72057594039107584, alloc unit ID 72057594043695104 (type In-row data): Page (1:312) could not be processed. See other errors for details. CHECKDB found 0 allocation errors and 2 consistency errors in table 'Trash' (object ID 581577110). CHECKDB found 0 allocation errors and 2 consistency errors in database 'TestMe'. repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (TestMe).
So now I have a corruption that will appear in every database I create. Keep in mind that this need not show up in a user created object. If any part of the [model] database becomes corrupt, and we’re not checking [model] with CHECKDB, then every database created will also be corrupt, and maybe unusable.
While we’re on the subject, here’s something even worse – while playing around with this concept, I noticed that in SQL Server 2014, DATA_PURITY checks are still *off* for [master] and [model] by default. So I created another test object in [model] and caused a data purity error in it. When I ran CHECKDB on [model], without specifically adding DATA_PURITY as an option, it came back clean. When I created a new database, I ran CHECKDB on it, and lo and behold – it threw a data purity error. So a corruption that was in model was not detected, and still propagated to a newly created database.
Have you hugged your [msdb], [model] and [master] databases today? If not, at least make sure you’re properly checking it for corruption using CHECKDB with DATA_PURITY. Your databases will thank you.
Thanks for reading.