We have recently move CPQ to SQL2014 AlwaysON cluster
we are seeing some serious problems with the synchronization from The Primary to one of the secondary servers (there are 2 secondary servers)
It seems to be fine for a while but then we start getting messages that Data Movement suspended on one or more databases
We have discovered by running this script we can see the amount of locks that are being held and what session-id is running holding them
SELECT request_session_id, COUNT (*) num_locks
FROM sys.dm_tran_locks
GROUP BY request_session_id
ORDER BY count (*) DESC;
GO
What we have found is that the session-id is holding a large number of locks (98535 at the moment)
We then found you can run this script
SELECT
[request_session_id] AS [SPID],
[resource_type] AS [LockType],
DB_NAME ([resource_database_id]) AS [DB],
[resource_description] AS [Resource],
[resource_associated_entity_id] AS [ResourceID],
[request_mode] AS [Mode],
[request_status] AS [Status]
FROM sys.dm_tran_locks
WHERE [request_session_id] < 0;
GO
and it will show you what SPID -3 is holding and all the locks are on one database (500+GB) with locktype of KEY or PAGE etc
Its not long after all the other database in same Availability Group and other AGs start failing.
I don't know how to release the locks except to stop and remove database from AG but that takes downtime which is a luxury. Any help or guidance is appreciated