--- PDF PAGE 315 ---
299
Suspect Pages
If SQL Server discovers a page with a bad checksum or a torn page, then it records the 
pages in the MSDB database in a table called dbo.suspect_pages. It also records any 
pages that encounter an 823 or 824 error in this table. The table consists of six columns, 
as described in Table 9-1.
The possible values for the event_type column are explained in Table 9-2.
Table 9-1.  suspect_pages Columns
Column
Description
Database_id
The ID of the database that contains the suspect page
File_id
The ID of the file that contains the suspect page
Page_id
The ID of the page that is suspect
Event_Type
The nature of the event that caused the suspect pages to be updated
Error_count
An incremental counter that records the number of times that the event 
has occurred
Last_updated_date
The last time the row was updated
Table 9-2.  Event Types
Event_type
Description
1
823 or 824 error
2
Bad checksum
3
Torn page
4
Restored
5
Repaired
7
Deallocated by DBCC CHECKDB
Chapter 9  Database Consistency

--- PDF PAGE 315 ---
299
Suspect Pages
If SQL Server discovers a page with a bad checksum or a torn page, then it records the 
pages in the MSDB database in a table called dbo.suspect_pages. It also records any 
pages that encounter an 823 or 824 error in this table. The table consists of six columns, 
as described in Table 9-1.
The possible values for the event_type column are explained in Table 9-2.
Table 9-1.  suspect_pages Columns
Column
Description
Database_id
The ID of the database that contains the suspect page
File_id
The ID of the file that contains the suspect page
Page_id
The ID of the page that is suspect
Event_Type
The nature of the event that caused the suspect pages to be updated
Error_count
An incremental counter that records the number of times that the event 
has occurred
Last_updated_date
The last time the row was updated
Table 9-2.  Event Types
Event_type
Description
1
823 or 824 error
2
Bad checksum
3
Torn page
4
Restored
5
Repaired
7
Deallocated by DBCC CHECKDB
Chapter 9  Database Consistency

--- PDF PAGE 320 ---
304
Unfortunately, the repair options of DBCC CHECKDB are not supported against 
memory tables. However, when you take a backup of a database that contains a memory-­
optimized filegroup, a checksum validation is performed against the files within this 
filegroup. It is therefore imperative that you not only take regular backups, but that you 
also check that they can be restored successfully, on a regular basis. This is because your 
only option, in the event of a corrupted memory-optimized table, is to restore from the 
last known good backup.
System Database Corruption
If system databases become corrupt, your instance can be left in an inaccessible state. 
The following sections discuss how to respond to corruption in the Master database and 
the Resource database.
Corruption of the Master Database
If the Master database becomes corrupted, it is possible that your instance will be unable 
to start. If this is the case, then you need to rebuild the system databases and then restore 
the latest copies from backups. Chapter 12 discusses strategies for database backups 
in more detail, but this highlights why backing up your system databases is important. 
In the event that you need to rebuild your system databases, you will lose all instance-­
level information, such as logins, SQL Server Agent jobs, linked servers, and so on, if you 
are not able to restore from a backup. Even knowledge of the user databases within the 
instance will be lost, and you will need to reattach the databases.
In order to rebuild the system databases, you need to run setup. When you are 
rebuilding system databases using setup, the parameters described in Table 9-3 are 
available.
Chapter 9  Database Consistency

--- PDF PAGE 321 ---
305
The PowerShell command in Listing 9-5 rebuilds the system databases of the 
PROSQLADMIN instance.
Listing 9-5.  Rebuilding System Databases
.\setup.exe /ACTION=rebuilddatabase /INSTANCENAME=PROSQLADMIN /SQLSYSADMINA
CCOUNTS=SQLAdministrator
As mentioned, when this action is complete, ideally we restore the latest copy of 
the Master database from a backup. Since we do not have one, we need to reattach our 
Chapter9 database in order to continue. Additionally, the detail of the corrupt page 
within the suspect_pages table will also be lost. Attempting to read the CorruptTable 
table in the Chapter9 database causes this data to be repopulated, however. The script in 
Listing 9-6 reattaches the Chapter9 database. You should change the file paths to match 
your own configuration before you run the script.
Table 9-3.  System Database Rebuild Parameters
Parameter
Description
/ACTION
Specifies Rebuilddatabase for the action parameter.
/INSTANCENAME
Specifies the instance name of the instance that contains the 
corrupt system database.
/Q
This parameter stands for quiet. Use this to run setup without any 
user interaction.
/SQLCOLLATION
This is an optional parameter that you can use to specify a 
collation for the instance. If you omit it, the collation of the 
Windows OS is used.
/SAPWD
If your instance uses mixed-mode authentication, then use this 
parameter to specify the password for the SA account.
/SQLSYSADMINACCOUNTS
Use this parameter to specify which accounts should be made 
sysadmins of the instance.
Chapter 9  Database Consistency

--- PDF PAGE 323 ---
307
After you select the instance that needs to be repaired, the following page of the 
wizard runs an additional rule check to ensure that the required features can be 
repaired. Finally, on the Ready To Repair page, you see a summary of the actions that are 
to be performed. After choosing to repair, you see the repair progress report. Once the 
repair completes, a Summary page displays, which provides you with the status of each 
operation that was performed and also a link to a log file that you may wish to review if 
you need to perform troubleshooting.
As an alternative to using SQL Server Installation Center, you can achieve the same 
rebuild from the command line. This is useful if your instance is running on Windows 
Server Core. When you are repairing an instance from the command line, the parameters 
available to you are those listed in Table 9-4. Because the Master database is not being 
rebuilt when you are repairing an instance, you do not need to specify a collation or 
Administrator details.
Table 9-4.  Instance Repair Parameters
Parameter
Description
/ACTION
Specifies Repair for the action parameter.
/INSTANCENAME
Specifies the instance name of the instance that contains the corrupt  
system database.
/Q
This parameter is quiet. Use this to run without any user interaction.
/ENU
An optional parameter that you can use on a localized operating system to 
specify that the English version of SQL Server should be used.
/FEATURES
An optional parameter you can use to specify a list of components to repair.
/HIDECONSOLE
An optional parameter that causes the console to be suppressed.
The PowerShell command in Listing 9-7 also rebuilds the PROSQLADMIN instance. This 
script also works for instances hosted on Windows Server Core.
Listing 9-7.  Repairing an Instance
.\setup.exe /ACTION=repair /INSTANCENAME=PROSQLADMIN /q
Chapter 9  Database Consistency