1

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.


2
  • It's a distributed transaction from MSDTC, so it's going to take it down all the associated sessions. See also mssqltips.com/sqlservertip/4142/… Apr 11, 2022 at 14:28
  • @Charlieface I had followed the instruction in this article before I posted this question. No "In doubt" Transaction was found. But thank you anyway. Apr 11, 2022 at 17:36

1 Answer 1

0

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.

Your Answer

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

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