这一次是否要从可用性组中删除数据库？哪一个？

Question

虽然修补我的一台服务器是可用性组的一部分，但作为辅助，我使用了几个问题 --真正的问题是可用性组中的一个虚拟服务器的主机在一到两分钟内耗尽了磁盘空间。

对于这个问题来说，问题是什么并不重要。

事实是：

1. 我在可用性组有两台机器，失去了第二台。
2. 可用性组涉及复制和我收到以下错误消息：

复制的事务正在等待下一个日志备份或镜像伙伴追赶

通过启动以下跟踪标志解决了该问题：

DBCC TRACEON(1448,-1)

1. 我找到了一些关于我的可用性组的信息，我可以看到一个服务器正在根据下面的脚本关闭: select * sys.dm_hadr_cluster_members

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

始终在脚本上的在这个问题上也没有告诉我太多

问题：

我在事务日志中落后了多少，赶上了一个永远不会回来的副本？

我需要信息来解决这个问题：

我应该从可用性组中删除数据库吗？

或者我应该构建一个新的服务器，将其添加到可用性组，等待日志赶上吗？

Answer 1

在此，我决定将数据库从可用性组中删除。为此，我使用了以下脚本：

-- 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