PowerShell and SQL Server Events–Monitoring DATABASE SUSPECT DATA PAGE


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 :

clip_image001Database 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 Alegre

[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

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Powershell, SQL SERVER EM GERAL, Virtual Pass BR. Bookmark the permalink.

1 Response to PowerShell and SQL Server Events–Monitoring DATABASE SUSPECT DATA PAGE

  1. Pingback: Querys do Dia a Dia: Monitoramento do Status das Databases e de Páginas Corrompidas | Fabrício Lima

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s