823/824/825 Errors - No Alert? Or is there?

PDinCA

Corruption errors 823, 824 and 825 seem to be very important.  I found no reference to these errors, or suspect_pages in the documentation, and there appears to be no pre-defined SQM Alert.

Is there some custom alert floating around anywhere?

How would Red Gate suggest incorporating such an alert, as distinct from what appears to be the "norm" of setting up SQL Agent alerts.  That looks like 2 tools for what ought to be possible with just one - SQL Monitor.

Thoughts/solutions involving SQM eagerly sought...

Russell D

So to answer the question briefly, no there isn't anything built in, unless you change the configuration for the SQL Server Error Log Entry Alert, which by default is set to a severity level of 20 or greater. These alerts are all raised with a severity 10, so you'd need to lower it, obviously though that could well generate more noise as there are a lot of alerts with a severity greater or equal to 10.

@PDinCA would something like this work for you, as a custom metric? It will return 0 for no matches, and 1 for any matches.

IF OBJECT_ID('tempdb..#suspect_pages') IS NOT NULL
    DROP TABLE #suspect_pages;

CREATE TABLE #suspect_pages
    (
      LogDate DATETIME ,
      ProcessInfo VARCHAR(1000) ,
      LogMessage TEXT
    );
 
INSERT  INTO #suspect_pages
        EXEC sys.xp_readerrorlog 0;
    
 
SELECT  COUNT(*)
FROM    #suspect_pages
WHERE LogMessage LIKE '%Error: 823%' OR LogMessage like '%Error: 824%' OR LogMessage like '%Error: 825%'

I have no doubt a DBA/better SQL writer could write something more effective but it seems to work for me, at least when I've simulated these errors. Eg this is probably better in a table variable but this was thrown together fairly quickly..

And obviously the user will need to be able to execute xp_readerrorlog.

Russell D

Its worth bearing in mind that it does do a count - here's the alert working as an example:



Given the severity of these alerts it may well be an idea to actually just clear the SQL Server Error Log (or at least start a new one) to start fresh. But see my edit below that now limits the error log scrape to the last hour.

Russell D

Ok this:

declare @endtime datetime = getdate()
declare @starttime datetime = dateadd(hh, -1, @endtime)

declare @suspect_pages table
(
    LogDate     datetime,
    ProcessInfo varchar(1000),
    LogMessage  text
);

insert into @suspect_pages
exec sys.xp_readerrorlog 0;

select count(*)
from @suspect_pages
where LogMessage like '%Error: 823%'
      or LogMessage like '%Error: 824%'
      or LogMessage like '%Error: 825%'
      and LogDate
      between @starttime and @endtime;

will limit the query to the last hour, and uses a table variable rather than a temporary table.

PDinCA

Thanks, Russell.  Certainly worth considering.  Brent Ozar's SQL Constant Care was the only mechanism that found the issue in one of our production databases (inconveniently on the 1st day of my vacation, so it was missed for 2 weeks, so we had no "good" backups readily available...).  Recommend his "corrupt" checklist, BTW...  sp_Blitz has some code for the msdb..suspect_pages content that may be adaptable, too.

Russell D

Cheers I will take a look at that. I believe that this really is something we should have in SQL Monitor as a default so I'll look in to how we can do that.

jackson321

here is a article on SQL Server error 823 & 825 https://www.systoolsgroup.com/updates/fix-sql-server-error-823-825/ please take a look into it.

nocerinom

Any news about this? Should I create the custom metric suggested by Russel?