March 5, 2013 at 2:13 am
Comments posted to this topic are about the item SQL 2012 Always On Status Report
Rudy
March 5, 2013 at 10:12 am
great script! Could you explain what the Low Water Mark for Ghosts column means?
March 5, 2013 at 10:16 am
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
Rudy
March 5, 2013 at 10:20 am
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?
March 5, 2013 at 10:29 am
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
Rudy
April 3, 2013 at 1:48 pm
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?
July 18, 2014 at 12:20 pm
SQLSentry can do that.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply