You are the database administrator of your company. The network contains a SQL Server 2008 computer thatstores several databases.
You want to identify all databases that have torn pages.
Which Transact-SQL statement should you run?
A.
SELECT database_id
FROM msdb..suspect_pages
WHERE event_type = 1;
B.
SELECT database_id
FROM msdb..suspect_pages
WHERE event_type = 2;
C.
SELECT database_id
FROM msdb..suspect_pages
WHERE event_type = 3;
D.
SELECT database_id
FROM msdb..suspect_pages
WHERE event_type = 4;
Explanation:
You should run the following Transact-SQL statement:
SELECT database_id FROM msdb..suspect_pages
WHERE event_type = 3;
The suspect_pages table is stored in the msdb database, and it contains information about suspect pages. Asuspect page is a page that the database engine fails to read due to an 823 or 824 error. However, the retrieveONLY torn pages, you need to use an event_type value of 3. An 823 error indicates a severe system-level errorthat threatens database integrity, such as a disk error. An 824 error indicates that SQL Server detected an I/Oerror based on logical consistency, such as a bad page ID. The status for each page in the
suspect_pages tableis listed in the event_type column. The pages with event_type value of 3 indicate torn pages. You should not run the following Transact-SQL statement:
SELECT database_id FROM msdb..suspect_pages
WHERE event_type = 1;
An event_type value of 1 indicates an 823 that results in a suspect page or an 824 error other than a badchecksum or a torn page. Bad checksum errors can be retrieved using an event_type value of 2. Torn pageerrors can be retreived using an event_type of 3. You should not run the following Transact-SQL statement:
SELECT database_id FROM msdb..suspect_pages
WHERE event_type = 2;
An event_type value of 2 indicates a bad checksum, not a torn page. You should not run the following Transact-SQL statement:
SELECT database_id FROM msdb..suspect_pages
WHERE event_type = 4;
An event_type value of 4 indicates a restored page, not a torn page.Objective:
Maintaining a SQL Server DatabaseSub-Objective:
Maintain database integrity.References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference > System Tables (Transact-SQL) > Backup and Restore Tables (Transact-SQL) > suspect_pages(Transact-SQL) TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Administration > BackingUp and Restoring Databases in SQL Server > Implementing Restore Scenarios for SQL Server Databases >Performing Page Restores > Understanding and Managing the suspect_pages Table