Resolve Orphan Transaction locking

Question

My SQL Server version is Microsoft SQL Server 2019 (RTM) and I faced a new locking problem.

An SSIS package was running. After a while it became suspended by a session with ID of (-2). I know this type of session belongs to an orphan transaction but I do not know how to solve it. The package is transactional and the transaction is DTC.

First of all I checked the detail of session in Activity Monitor and it was:

sys.sp_describe_first_result_set

It mean nothing for me. Why a system procedure?

Then I try KILL the orphan session using:

Select req_transactionUOW
 from master..syslockinfo
 where req_spid = -2

KILL '93ED58CC-5D18-4A08-82FC-1AE9A40E4E3C'

Which '93ED58CC-5D18-4A08-82FC-1AE9A40E4E3C' is the output of SELECT query.

But it kills the package session and orphan transaction together.

---

Answer 1

you could find any open transactions by executing this script

USE TestDB
Go
DBCC OPENTRAN 

after executing you may view the orphaned transaction like this:

Transaction information for database 'TestDB'.

Replicated Transaction Information:

Oldest distributed LSN : (0:0:0)

Oldest non-distributed LSN : (5523658:5048:2)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

As you can see, we have an open orphaned transaction in the database that is related to CDC objects and cleans-up jobs. we could disable CDD and enable it again

USE TestDB
go
exec sys.sp_cdc_disable_db

Since we did a manual cleanup of the objects, we should disable and enable to allow SQL Server to do a complete cleanup of the CDC objects and settings. Once that is done, you can check again for any open transactions.

This trick may help you do a manual cleanup of CDC if this is causing issues with your transaction log size. After you have things working again, you may want to shrink the log file to the proper size to get back some disk space.