Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Untangle TempDB Performance with SQL Diagnostic Manager - Free Webinar
 

Importance of Performing DBCC CHECKDB on all SQL Server Databases


By:   |   Last Updated: 2016-12-28   |   Comments   |   Related Tips: 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

Backup the SQL Server Model Database

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

Restore SQL Server Model Database

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

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
SQL Server Startup Parameters to Restore the Model Database

You cannot use DAC (Dedicated Administrator Connection) to perform a database backup or restore because this operation is not supported as shown below.

Do not use DAC to perform database backup or restore because this operation is not supported.

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.

Model database has been left offline

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


Last Updated: 2016-12-28


next webcast button


next tip button



About the author
MSSQLTips author Simon Liew 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.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools