Commands around Suspect pages in a database

[wsmelton]

Feature Request

A simple command that just pulls readable information from msdb..suspect_pages.

Problem to solve

At times you need to monitor the suspect pages a server may have or verify after you get logical errors returned from DBCC results, or even finding the dreaded 823/824 errors in the error log.

You can run SELECT * FROM msdb..suspect_pages but that gives you a bunch of values that are all int and bigint, and one datetime value. So purpose would be to make it readable and provide full descriptive value for each ID.

Other noteworthy things to mention that could lead to commands:

• The table has a limit of 1k rows
• The table, being that it has a limit, is most commonly cleared after the pages previously recorded as a suspect page have been fixed. (e.g. Clear-DbaSuspectpages)

Each command below should allow multiple server and database names to be passed in.

~Get-DbaSuspectPages~

It does not require reference to Db or Database in the command since I believe the table sits in msdb so it is a server-level "object" technically. We could have a -Database filter on it if desired.

I would also have to believe the name of the command is kept as plural because the object it is reading is called "pages" and not "page"...but I'll defer to the boss on that decision.

The information returned from the table itself should be translated into readable information. There should be commands already in the module that can translate these IDs, with the exception of the page ID value.

Column name	Data type	Description
database_id	int	ID of the database to which this page applies.
file_id	int	ID of the file in the database.
page_id	bigint	ID of the suspect page. Every page has a page ID that is a 32-bit value identifying the location of the page in the database. The page_id is the offset into the data file of the 8 KB page. Each page ID is unique in a file.
event_type	int	The type of error; one of: 1 = An 823 error that causes a suspect page (such as a disk error) or an 824 error other than a bad checksum or a torn page (such as a bad page ID). 2 = Bad checksum. 3 = Torn page. 4 = Restored (page was restored after it was marked bad). 5 = Repaired (DBCC repaired the page). 7 = Deallocated by DBCC.
error_count	int	Number of times the error has occurred.
last_update_date	datetime	Date-and-time stamp of the last update.

Clear-DbaSuspectPages

Offer the ability to clear only those pertaining to a certain ID values, or the whole table.

Test-DbaSuspectPages

Basic command that just groups the data found in the table by each database and event type then returns the count of each.

[gbargsley]

I have the function working in test. Question on your notes.

Should we make a Clear function? The wording on MSDN says that as the suspect pages are fixed the table is updated. Could it cause issues removing records?

I will see if I can get the logic worked out for the grouping for Test function.

[potatoqualitee]

(btw @ConstantineK is almost done with his database breaker)

[wsmelton]

@gbargsley yes you do need to clear that table. The record in the table is updated when the page is repaired, but it does not purge that record from the table. The table itself has a record limit and if you never clear that table you will lose any new record of a new suspect pages. SQL Server will never clear a "repaired" page from that table, it has to be done manually.

[gbargsley]

Create new command to accompany Restore-DbaDatabase page restore parameter.

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/manage-the-suspect-pages-table-sql-server

[wsmelton]

I'm not sure I follow the comment @gbargsley , what command name are you proposing or is this referring to a Clear-DbaSuspectPage ?

[gbargsley]

I was creating another issue this morning and when I looked at this issue for reference I saw it was still open. So I put a note from comments from Stuart on Slack. This is for the Clear command I was going to work on. Sorry for confusion, but was just putting a note for my reference.

[wsmelton]

https://docs.dbatools.io/#Get-DbaSuspectPage has been done.

[andreasjordan]

So Clear-DbaSuspectPages and Test-DbaSuspectPages are missing? @gbargsley : Are you working on this? Do you need help?

[wsmelton]

If anyone is interested in picking this up after 2.0 release please create a new feature issue.