How to get Availability Groups connection Report

Answers

• Alex B Redgate › Posts: 1,157 Diamond 4

  September 28, 2020 11:46AM

  There isn't a built in way to do this unfortunately, but you may be able to get the information you need with a custom metric.  You would need to use something like xp_readerrorlog to get the information into a temp table and then count the number of rows in that temp table that would then be the value returned to the custom metric.
  
  Something like this would count the number of entries with that text in the current errorlog:
  

  CREATE TABLE #ErrorLogEntries
  (Logdate DATETIME,
  Process VARCHAR(20),
  Text VARCHAR(4000))
  
  INSERT INTO #ErrorLogEntries
  EXEC xp_readerrorlog 0, 1, N'Always On Availability Groups connection with secondary database terminated for primary database'
  
  SELECT COUNT(Text) AS EntryCount
  FROM #ErrorLogEntries
  
  DROP TABLE #ErrorLogEntries

  
  This is only in the current errorlog, so you could modify the above to do several inserts from the most recent errorlogs perhaps (this is the first parameter - 0 is current, 1 is first archived, etc.)
  
  And you would only need to run this against the master database, so it would look something like this:
  ​
  
  If you just need to see the numbers quickly you can use the test metric on this page to see the current values.
  
  You would then be able to add a report tile of "Analysis graph" type for that custom metric to see this in a report.
  
  That result is going to be as good as the query that is written though, and depends on the rolling over of the errorlog files, so it would be good to make the suggestion on the SQL Monitor Uservoice forum here for having a built in manner to do this: https://sqlmonitor.uservoice.com/forums/91743-suggestions/filters/top

  Product Support Engineer | Redgate Software
  
  Have you visited our Help Center?

  0 · Share on Twitter