SQL 2014 SP2 CU1 - AlwaysOn Cluster trouble synching to one node

Question

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

Answer 1

SPID -3 is a deferred transaction or BOL Definition.

A deferred transaction is one where the transaction could not be recovered fully because of an I/O error or a gross file system problem like an unavailable file.

Check your disk subsystem and run a CHECKDB in your database.

To kill the negative spid, use

SELECT DISTINCT 'KILL ''' + CONVERT(VARCHAR(100),request_owner_guid) + ''';'
FROM   sys.dm_tran_locks
WHERE  request_session_id = -3
AND    database_id = DB_ID('Your_DBName');