Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL 2012 Always On Status Report Expand / Collapse
Author
Message
Posted Tuesday, March 5, 2013 2:13 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 11:33 AM
Points: 312, Visits: 1,099
Comments posted to this topic are about the item SQL 2012 Always On Status Report


Post #1426651
Posted Tuesday, March 5, 2013 10:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 7, 2014 7:51 AM
Points: 119, Visits: 541
great script! Could you explain what the Low Water Mark for Ghosts column means?

Dustin W. Jones - Database Tech.
Post #1426921
Posted Tuesday, March 5, 2013 10:16 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 11:33 AM
Points: 312, Visits: 1,099
Hello and thanks for your comment.

Below is the answer from Microsoft: http://msdn.microsoft.com/en-us/library/ff877972.aspx

"A monotonically increasing number for the database indicating a low water mark used by ghost cleanup on the primary database. If this number is not increasing over time, it implies that ghost cleanup might not happen. To decide which ghost rows to clean up, the primary replica uses the minimum value of this column for this database across all availability replicas (including the primary replica)."

Hope this helps :)

Rudy



Post #1426927
Posted Tuesday, March 5, 2013 10:20 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 7, 2014 7:51 AM
Points: 119, Visits: 541
Thanks for the quick response. I'm looking for a way (in a DMV) to see how far the secondary replica has fallen behind under heavy database load (inserts, updates, deletes) on the primary. In other words, is there a way to see how many transactions the secondary needs to apply so it is caught up and in sync with the primary?

Dustin W. Jones - Database Tech.
Post #1426931
Posted Tuesday, March 5, 2013 10:29 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 11:33 AM
Points: 312, Visits: 1,099
Hmmm, good question! I have to look into this a bit further as I don't know.

But I will let you know what I find.

Thanks,

Rudy



Post #1426932
Posted Wednesday, April 3, 2013 1:48 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 10:20 AM
Points: 3, Visits: 199
We have HADR running in production and I am trying to get alerting running when it fails over. I think I will be able to use primary_replica for this and send out emails from SQL.

If anyone knows of a monitoring tool that can detect when HADR fails over please let me know. We currently have Solarwind and their template for 2012 is exactly the same as 2008 so they don't have any new counters for the new features.

PS: I removed select distinct because this does not return duplicates

SELECT
gs.primary_replica as 'Primary Server',
secondary_recovery_health_desc as 'Secondary Server Health Status',
synchronization_state_desc as 'Synchronization State',
database_state_desc as 'Database State',
suspend_reason_desc as 'Suspended Reason',
last_sent_time as 'Last Data Send Time',
last_received_time as 'Last Data Received TIme',
last_hardened_time as 'Last Hardened Time',
last_redone_time as 'Last Redone Time',
log_send_queue_size as 'Log Send Queue Size',
log_send_rate as 'Log Send Rate',
redo_queue_size as 'Redo Queue Size',
redo_rate as 'Rate of Redo',
filestream_send_rate as 'Filestream Send Rate',
last_commit_time as 'Last Commit Time',
low_water_mark_for_ghosts as 'Low Water Mark for Ghosts'
FROM sys.dm_hadr_availability_group_states as gs
JOIN sys.dm_hadr_database_replica_states as dd ON gs.group_id = dd.group_id
ORDER BY gs.primary_replica DESC



---
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #1438564
Posted Friday, July 18, 2014 12:20 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 10:08 AM
Points: 260, Visits: 1,047
SQLSentry can do that.
Post #1594190
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse