How to get Availability Groups connection Report

How can I get a report that show how often I am getting this error "Always On Availability Groups connection with secondary database terminated for primary database "
Thanks

Answers

  • 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?
Sign In or Register to comment.