Importance of Performing DBCC CHECKDB

DBCC CHECKDB - SeniorDBA

You absolutely need to be performing a DBCC CHECKDB on all of your databases on a regular schedule. This includes the system databases. One example is Model database corruption leading to a TempDB issue, because the TempDB is created using the Model Database.

In this article by Simon Liew we get a better understanding of the potential issues by example.

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s