1

I have found out the msdb.dbo.suspect_pages has 15 entries. 14 of those are last updated in 2014 and of type 7 ("Deallocated"). One entry is of type 1 (823 or 824 error) with a count of 1 and last udpated in June 2015.

Last DBCC CheckDB was ran on Nov. 9th using a maintenance plan. The only output I have is the history of this job saying it completed 100%:

Microsoft (R) SQL Server Execute Package Utility Version 10.50.6000.34 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 7:54:37 PM Progress: 2016-11-09 19:54:44.66 Source: {84CA0A16-9095-4D9D-9CF8-4AF8A1144015}
Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress Progress: 2016-11-09 19:54:47.22 Source: Check Database Integrity Executing query "USE [dbname] ".: 50% complete End Progress Progress: 2016-11-09 22:08:15.87 Source: Check Database Integrity Executing query "DBCC CHECKDB(N'dbname') WITH NO_INFOM...".: 100% complete End Progress DTExec: The package execution returned DTSER_SUCCESS (0). Started: 7:54:37 PM Finished: 10:08:16 PM Elapsed: 8018.92 seconds. The package executed successfully. The step succeeded.

So there is not further detailed information about the possible corruption's source / table / index. I am not sure if we found any issues here or not jsut because it completed 100%...

I ran "sp_blitz" and "sp_blitzindex" but it did not return any corruption or something like that.

So far there were no suspect failures or anything that maybe caused by corruption. Since it is there for many months I'm not sure if any action must be taken. Of course there are no backup existing prior that date...

I wonder where to look at next?

Edit:

Another run of DBCC Checkdb as indicated by @Randolph returned the following:

Message DBCC CHECKDB (myDBName) WITH all_errormsgs, no_infomsgs, data_purity executed by NT AUTHORITY\SYSTEM found 0 errors and repaired 0 errors. Elapsed time: 1 hours 23 minutes 27 seconds. Internal database snapshot has split point LSN = 007c5657:00000372:000e and first LSN = 007c5657:00000353:0001.

Are we okay? Should I delete the entries from msdb.dbo.suspect_pages ?

3

If this returns nothing, then your database is fine.

DBCC CHECKDB (DatabaseName) WITH NO_INFOMSGS,
ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS, DATA_PURITY
  • This is the result: Message DBCC CHECKDB (myDBName) WITH all_errormsgs, no_infomsgs, data_purity executed by NT AUTHORITY\SYSTEM found 0 errors and repaired 0 errors. Elapsed time: 1 hours 23 minutes 27 seconds. Internal database snapshot has split point LSN = 007c5657:00000372:000e and first LSN = 007c5657:00000353:0001. – Magier Nov 16 '16 at 7:55
  • 0 errors. Magical. – Randolph West Nov 17 '16 at 1:58
  • I am happy too :) But I wonder, please see question edit, what to do? SqnSentry still raises High priorized alerts. Should I clear the suspect_pages table out? – Magier Nov 17 '16 at 10:22
  • Clear those out from the table. – Randolph West Nov 19 '16 at 2:21
2

Since you have event_type = 7 ==> meaning it is marked as deallocated by DBCC.

Any deallocated data is no longer accessible or recoverable for the user, and the exact contents of the deallocated data cannot be determined. Therefore, referential integrity may not be accurate after any rows or pages are deallocated because foreign key constraints are not checked or maintained as part of this repair operation. The user must inspect the referential integrity of their database (using DBCC CHECKCONSTRAINTS) after using the REPAIR_ALLOW_DATA_LOSS option.

So as an immediate step (when you have a maintenance window), run DBCC CHECKDB as @Randolph suggested above.

As a precaution, create important alerts - sev 19 through 25 along with 823,824,825 and others.

Remember that a DBA is responsible for managing that table by regularly pruning it.

from BOL :

We recommend that you periodically delete or archive rows that have an event_type of restored or repaired, or rows that have an old last_update value.

  • thanks. I have all of those Alerts already in place, but the corruption I found is older than the alerts I have setup when I came in. – Magier Nov 15 '16 at 11:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.