2

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

4
  • This is a locking problem on the secondary? And read-routing is enabled?
    – Nic
    Sep 20, 2016 at 19:27
  • The database in question is likely in recovery on the secondary replica. This -3 indicates a deferred recovery transaction (ref). In your case, you may want to drop/re-add the DB to the Availability Group as you likely are experiencing some level of corruption. Sep 20, 2016 at 19:28
  • 1
    Are you getting this information from the primary or secondary servers? Does this coincide with a recent failover (planned or unplanned)? Sep 20, 2016 at 21:18
  • What @Sean said ^^^. If this is an issue with a Secondary Replica, you're looking at no downtime to resolve (e.g. drop/restore) as you likely don't have an issue on the primary. Sep 21, 2016 at 15:51

1 Answer 1

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');
4
  • I would expect the UOW to coincide with '00000000-0000-0000-0000-000000000000' so I wouldn't recommend killing that. Instead drop the db and restore it from backup and then add it back to the availability group. Sep 20, 2016 at 19:30
  • Well offline and online the database might get rid of deferred transaction spid. The database is 500GB, so restoring from backup and putting in AG would be difficult.
    – Kin Shah
    Sep 20, 2016 at 19:34
  • It depends on if the recovery percentage being reported in the ERRORLOG is moving along or not. If it's stuck, it's likely stuck forever and a drop/restore is probably the quickest way to get the AG healthy again, regardless the amount of time needed for a recovery. Sep 20, 2016 at 19:38
  • Yes I know that dropping and restoring.. or in this case it would be dropping the database from the availability group and dropping from secondaries and then readding to ag.. but I guess my question is .. then it will happen again. and I need to know how to prevent it and I only have this problem on 1 of the secondarys the other never suspends. Sep 20, 2016 at 19:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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