We know if a table is corrupt when we perform a simple select and a error like this happens.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xe61f1887; actual: 0xe31f1887). It occurred during a read of page (1:78) in database ID 8 at offset 0x0000000009c000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DBPageSuspect.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.
Then we can get more information in suspect_pages table in msdb.
But it would be interesting if we could be notified by email when this error happen. For this we have the class Database Suspect Data Page Event Class. This event is triggered every time a line is added in the table suspect_pages.
According to the BOL this is the columns that we have when the event is triggered :
Database Suspect Data Page Event Class Data Columns
|
Data Column Name |
Data Type |
Description |
Column ID |
Filterable |
|
DatabaseID |
int |
ID of the database for which the suspect page event has been raised. This is the same as the database_id column of the suspect_pages table. |
3 |
Yes |
|
EventClass |
int |
The type of the event is 213. |
27 |
No |
|
EventSequence |
int |
Sequence of event class in batch. |
51 |
No |
|
SPID |
int |
ID of the SQL Server task that encountered the suspect page. |
12 |
Yes |
|
StartTime |
datetime |
Time that the event occurred. |
14 |
Yes |
|
ObjectID |
int |
ID of the database file that contains the suspect page. This is the same as the file_id column of the suspect_pages table. |
22 |
Yes |
|
ObjectID2 |
int |
ID of the suspect page in the file. This is the same as the page_id column of the suspect_pages table. |
56 |
Yes |
|
Error |
int |
Type of error that was encountered . This value is the same as the event_type value for the page in the suspect_pages table. |
31 |
Yes |
Then, we can work with this information :
I will just put the code to trigger the event, because to corrupt a page is a process that gets better showing. In the video below I will show the complete process from corrupting the page to send e-mail.
The code to register the event :
$query = "select * from DATABASE_SUSPECT_DATA_PAGE"
Register-WMIEvent -Namespace 'root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER' -Query $query -Action {ipmo sqlevents ;ipmo functions;Send-SMTPmail -smtpserver 'smtp.mail.yahoo.com.br' -from 'juniorlaerte@yahoo.com.br' -to 'laertejuniordba@hotmail.com' -subject 'Event Notification - Suspect Pages' -body "Erro Details : `n`n
Database ID : $($event.SourceEventArgs.NewEvent.Databaseid) `n
SPID : $($event.SourceEventArgs.NewEvent.spid) `n
Start Time : $([Management.ManagementDateTimeConverter]::ToDateTime($event.SourceEventArgs.NewEvent.StartTime)) `n
File ID : $($event.SourceEventArgs.NewEvent.ObjectID) `n
Page ID : $($event.SourceEventArgs.NewEvent.ObjectID2) `n
Error : $( switch ($event.SourceEventArgs.NewEvent.Error)
{
1 {'823 error caused by an operating system CRC error or 824 error other than a bad checksum or a torn page (for example, a bad page ID)'}
2 {'Bad checksum'}
3 {'Torn Page'}
4 {'Restored (The page was restored after it was marked bad)'}
5 {'Repaired (DBCC repaired the page)'}
7 {'Deallocated by DBCC'}
}
)
" }
Watch de Video and enjoy 
[Updated] Fabricio Lima did a blog post about monitoring suspect data pages using T-SQL, you can see in here –> Querys do Dia a Dia: Monitoramento do Status das Databases e de Páginas Corrompidas
#PowerShellLifeStyle
$hell Your Experience !!! 
Pingback: Querys do Dia a Dia: Monitoramento do Status das Databases e de Páginas Corrompidas | Fabrício Lima