By: Simon Liew | Updated: 2016-12-28 | Comments | Related: More > System Databases
Problem
The SQL Server model database is one of the core system databases, but it seem to get less attention in terms of importance. This tip will demonstrate the havoc the model database can cause when database integrity checks exclude the model database and corruption goes undetected.
Solution
The model database is used as the template for all user databases created on an instance of SQL Server. When a new database is created, the entire contents of the Model database including database options are copied to the new database. Tempdb also inherits some settings from the model database when the SQL Server service starts up.
Corruption on system and user databases other than the model database might be noticeable very quickly, but corruption in the model database can stay hidden until an event such as the SQL Server service is restarted or a new database is created.
Corruption in the SQL Server Model Database
The scenario starts with a perfectly healthy SQL Server 2016 Developer Edition RTM.
Page id 164 belongs to system table [sys].[sysidxstats] (equivalent to system view sysindexes) in the model database. This page is corrupted and then a full backup is taken. You can download the corrupted full model database backup at the end of this tip if you wish to simulate the steps in this tip.
Unless the BACKUP command is specified with the option CHECKSUM, generation of backup checksums and the validation of page checksums is disabled. Hence, the BACKUP command seems to execute successfully without error even though this model database contains a corrupted page.
BACKUP DATABASE [model] TO DISK = 'F:\SQLDATA\model_corrupt_sql2016.bak' WITH COMPRESSION
Step 1 - Setting up a corrupt model database
To simulate the scenario in this tip, you can replace the model database in your SQL Server 2016 development/test instance using the corrupt model database backup provided at the bottom of this tip. Restoring the model database works the same as restoring a user database.
IMPORTANT NOTE: Make sure a database integrity check is executed and a full backup of the model database is performed on your SQL Server instance prior to attempting this RESTORE. This way you can restore your original model database after testing.
USE master GO RESTORE DATABASE [model] FROM DISK = 'F:\SQLDATA\model_corrupt_sql2016.bak' WITH REPLACE
If you query the msdb.dbo.suspect_pages table, you will not find any entries for suspect pages. SQL Server does not access every data page to check for suspect page automatically. A suspect page is reported when the page is accessed during regular processing and an abnormality is encountered such as a bad checksum or inability to read the page on disk. So, a corrupt page can go undetected if the page is never accessed since the corruption occurred.
select * from msdb.dbo.suspect_pages
Step 2 - Restart the SQL Server Service
A planned outage on the SQL Server instance occurs and the SQL Server service is restarted. SQL Server service starts up and then goes back to a stopped state.
When you check the SQL Server Error Log on the file system, you find an error that states the TempDB database (database ID 2) is corrupt on page id 164 and SQL Server initiated a shutdown.
We know the root cause is not due to TempDB corruption, but rather the corrupt model database. In a production environment, this might not be obvious because you cannot perform database integrity checks on model, TempDB or any user defined databases since SQL Server is inaccessible.
2016-10-31 16:04:31.12 spid3s Error: 824, Severity: 24, State: 2. 2016-10-31 16:04:31.12 spid3s SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xf19e4700; actual: 0xf19e47ad). It occurred during a read of page (1:164) in database ID 2 at offset 0x00000000148000 in file 'E:\TempDB\MSSQL13.MSSQLSERVER\MSSQL\Data\tempdb.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. 2016-10-31 16:04:31.12 spid3s SQL Server shutdown has been initiated
One option is to check the model database for corruption by restoring the model database backup on another SQL Server instance with a different database name and perform the database integrity checks there.
Step 3 - Restore Model Database
The combination of the two trace flags below will allow the SQL service to be started:
- T3608 – Recovering only Master database. If activities that require TempDB are initiated, then model is recovered and TempDB is created
- T3609 – Recovering all databases, but not clearing TempDB
You cannot use DAC (Dedicated Administrator Connection) to perform a database backup or restore because this operation is not supported as shown below.
Connect to the SQL Server instance and restore the model database from a backup which is not corrupt. An error message will indicate the model database is left in an OFFLINE state after the RESTORE. If you start-up the SQL Server service now, SQL Server will go back to a stopped state. The error in SQL Server log will be as below.
2016-11-01 09:37:12.49 spid6s Error: 942, Severity: 14, State: 4. 2016-11-01 09:37:12.49 spid6s Database 'model' cannot be opened because it is offline. 2016-11-01 09:37:12.49 spid6s SQL Server shutdown has been initiated
An ALTER DATABASE command needs to be issued to set the model database to ONLINE and MULTI_USER.
You might see an error message when SQL Server tries to perform a cleanup of replication objects. This is due to the trace flag that is still in effect to skip recovery on the model database when the database is restored.
Remove the two trace flags (3608 and 3609) from the SQL Server startup parameters and restart the SQL Server service. Perform a database integrity check on the model database and the command should execute successfully without an error.
No backup of model database
In the situation where you do not have a backup of the model database or all model database backups contain corruption, you can copy the model data and log file from another working instance with the same SQL Server version and collation. This will allow the SQL Server instance to start up for you to perform further checks.
Summary
The official documentation to rebuild system databases (Master, Model, MSDB, or Resource) is to run Setup.exe with ACTION=REBUILDDATABASE.
In this tip specifically, we choose to RESTORE the model database because we knew the root cause and we had a backup of the model database prior to corruption. You can see that it can get very complicated to diagnose and recover from corruption in the model database, even when the model database can be restored to a user defined database.
I hope this tip encourages everyone to treat the model database with importance just like the other system databases.
Integrity checks on Tempdb is no exception if your organization wants every opportunity to know when something starts to go wrong. Database integrity checks performed on Tempdb will not check the SQL Server catalog or Service Broker consistency because a database snapshot could not be created or because WITH TABLOCK was specified. However, it can still perform a variety of checks on Tempdb and it is fully supported.
Next Steps
- This tip will show you step by step the process of Restoring SQL Server system databases msdb and model.
- Official documentation to Rebuild System Databases
- Diagnostic Connection for Database Administrators
- Replication error message MSSQL_ENG003165
- Download full backup of corrupt Model database backup to simulate this tip.
About the author
Simon Liew is an independent SQL Server Consultant in Sydney, Australia. He is a Microsoft Certified Master for SQL Server 2008 and holds a Master’s Degree in Distributed Computing.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2016-12-28
