0

While patching one of my servers that is part of an availability group, as a secondary, I had some problems - the real problem was that the host machine of one of the virtual servers in the availability group - run out of disk space for a brief minute or two.

It does not matter what was the problem, for this question.

The facts are:

  1. I had 2 machines on the availability group, lost the secondary.

  2. The availability group was involved in replication and I was getting the following error message:

Replicated transactions are waiting for next Log backup or for mirroring partner to catch up

which was solved by starting the following trace flag:

DBCC TRACEON(1448,-1)
  1. I find some info about my availability group, and I can see one server is down according to the scripts below:

    select * from sys.dm_hadr_cluster_members

enter image description here

SELECT
gs.primary_replica as 'Primary Server',
DB_NAME(dd.database_id) as 'Database Name',
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
left outer JOIN sys.dm_hadr_database_replica_states as dd ON gs.group_id = dd.group_id
ORDER BY gs.primary_replica DESC, DB_NAME(dd.database_id) DESC

enter image description here

The always On script at this question does not tell me much either

QUESTION:

How much far behind am I in the transaction log, catching up with a replica that will never come back?

I need information to solve this question:

Should I remove the databases from the availability group?

or should I build a new server, add it to the availability group, wait for the logs to catch up?

  • 2
    In regard to how far behind where catching up isn't possible - it is different for each environment and workload. If you have historical stats on send rate and redo rate then we can do some easy math, otherwise it's a best guess kind of situation :( – Sean Gallardy Dec 12 '18 at 0:39
0

I have decided to remove the databases from the availability group, on this occasion. for this I have used the following scripts:

-- https://blog.pythian.com/list-of-sql-server-databases-in-an-availability-group/
-- How do we know that this instance is part of Availability Group(s):
SELECT
AG.name AS [Name],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
ISNULL(arstates.role, 3) AS [LocalReplicaRole]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
    ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
    ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
    ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
ORDER BY [Name] ASC

-- https://blog.pythian.com/list-of-sql-server-databases-in-an-availability-group/
-- How do we get some information about the databases in the Availability Group:
SELECT
AG.name AS [AvailabilityGroupName],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
ISNULL(arstates.role, 3) AS [LocalReplicaRole],
dbcs.database_name AS [DatabaseName],
ISNULL(dbrs.synchronization_state, 0) AS [SynchronizationState],
ISNULL(dbrs.is_suspended, 0) AS [IsSuspended],
ISNULL(dbcs.is_database_joined, 0) AS [IsJoined],
remove_database='USE [master]' + char(13) + 'ALTER AVAILABILITY GROUP ' + quotename(AG.name ) + char(13) + 'REMOVE DATABASE ' + quotename(dbcs.database_name) + char(13)
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
   ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
   ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
   ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
   ON arstates.replica_id = dbcs.replica_id
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs
   ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id
ORDER BY AG.name ASC, dbcs.database_name

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.