Negative session id in mssql
Negative SPID happened when someone has been killed a distributed transaction SPID. An orphaned distributed transaction SPID to be totally precise.
Abort In Doubt Transactions
Computers
My Computer
Distributed Transaction Coordinator --->
Then find the appropriate DTC service. Below you can see there are several running on this server, both Local DTC and Clustered DTC. Once you have the correct DTC, go to Transaction Statistics and look at the In Doubt transactions in the Current section as shown.We have highlighted in fig.
https://i.stack.imgur.com/kswXT.jpg
Identity Negative session id in SSMS:
This session ID makes it easier to identify orphaned transactions by querying the session ID column in sys.dm_tran_locks, sys.dm_exec_sessions, or sys.dm_exec_requests dynamic management views.
negative session occurs in mssql using query.
SELECT REQUEST_MODE, REQUEST_TYPE, REQUEST_SESSION_ID
FROM sys.dm_tran_locks
WHERE RESOURCE_TYPE = 'OBJECT'
AND RESOURCE_ASSOCIATED_ENTITY_ID =(SELECT OBJECT_ID('System'))
get WOW session :
USE Master;
GO
SELECT DISTINCT(request_owner_guid) as UoW_Guid FROM sys.dm_tran_locks WHERE request_session_id =-2
GO
blocking_session_id ID of the session that is blocking the request. If this column is NULL, the request is not blocked, or the session information of the blocking session is not available (or cannot be identified).
-2 = The blocking resource is owned by an orphaned distributed transaction.
-3 = The blocking resource is owned by a deferred recovery transaction.
-4 = Session ID of the blocking latch owner could not be determined at this time because of internal latch state transitions.
UOW values, This will return a 32 digit UOW number.
Notes :
1. If you find multiple UOW numbers for single SPID, kill them one by one.
GUID format sample.
https://i.stack.imgur.com/U547K.jpg
2. In case, these steps failed to resolve your issue or you did not find any UOW then restart SQL Service and MS DTC Service.
3. If you are facing these issues very frequently, then engage your application team for code analyze.
DTCPing,WinRM / RMClient and DTCTester. All the tools used showed us that MSDTC was working fine without any problem.
DTCTester** **
Tests a distributed transaction against a specified Microsoft SQL Server. This tool helps to test distributed transactions across firewalls or against networks.
DTCTester performs distributed transactions by using ODBC API against a SQL Server database.