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

• 
  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.
  
  

  <div>IF OBJECT_ID('tempdb..#suspect_pages') IS NOT NULL
  &nbsp; &nbsp; DROP TABLE #suspect_pages;
  
  CREATE TABLE #suspect_pages
  &nbsp; &nbsp; (
  &nbsp; &nbsp; &nbsp; LogDate DATETIME ,
  &nbsp; &nbsp; &nbsp; ProcessInfo VARCHAR(1000) ,
  &nbsp; &nbsp; &nbsp; LogMessage TEXT
  &nbsp; &nbsp; );
  &nbsp;
  INSERT&nbsp; INTO #suspect_pages
  &nbsp; &nbsp; &nbsp; &nbsp; EXEC sys.xp_readerrorlog 0;
  &nbsp; &nbsp;&nbsp;
  &nbsp;
  SELECT&nbsp; COUNT(*)
  FROM&nbsp; &nbsp; #suspect_pages
  WHERE LogMessage LIKE '%Error: 823%' OR LogMessage like '%Error: 824%' OR LogMessage like '%Error: 825%'</div>

  
  
  

  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 July 20, 2018 13:32

  - 0 +
• 
  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 July 20, 2018 14:55

  - 0 +
• 
  Ok this:
  
  

  <div>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;<br></div>

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

  Russell D July 20, 2018 15:16

  - 0 +
• 
  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.

  PDinCA July 20, 2018 17:02

  - 0 +
• 
  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.

  Russell D July 23, 2018 14:48

  - 0 +
• 
  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.

  jackson321 March 13, 2019 05:51

  - 0 +
• 
  Any news about this? Should I create the custom metric suggested by Russel?

  nocerinom January 19, 2021 12:52

  - 0 +

Add comment

Please sign in to leave a comment.