I have this open transaction, according to DBCC OPENTRAN:
Oldest active transaction:
SPID (server process ID) : 54
UID (user ID) : -1
Name : UPDATE
LSN : (4196:12146:1)
Start time : Jul 20 2011 12:44:23:590PM
SID : 0x01
Is there a way to kill it/ roll it back?
In cases of deadlock, the following query should be run at regular intervals.
DBCC opentran()
If then the same SPID number is returned multiple times in the following report
Oldest active transaction:
SPID (server process ID): 131
UID (user ID) : -1
Name : implicit_transaction
LSN : (634998:226913:1)
Start time : Jan 19 2022 6:36:36:360PM
SID : 0x010500000000000515000000c6bb507a9dbeda5275b975547b3e0000
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Completion time: 2022-01-19T18:36:38.8421769+03:00
Then make a detail query for this transaction. It is critical to permanently resolve the source of this problem.
exec sp_who2 131
exec sp_lock 131
After investigating the cause, you can resolve the deadlock by killing that process.
KILL 131
If you want to see all SPIDs and blocked as tables, you should use the following query.
SELECT spid, blocked,[dbid],last_batch,open_tran
FROM master.sys.sysprocesses
WHERE open_tran <> 0
I ended up running into the situation of locking up a sessions as reported by DBCC OPENTRAN but due to the corporate lock down of the Server/database my ability to KILL was not available.
I discovered that the app I was using to execute the script(s), VS 2022, was complicit, so to speak, in keeping the transactions alive. By closing the app, it notified me that there were active sessions running and that closing could have consequences. By accepting the notifications and closing the app, the open transactions would subsequently be closed.