And start time the transaction

Chapter 12 ■ transaCtions, LoCking, BLoCking, and deadLoCking

The following are some thoughts and recommendations regarding how to handle transactions in your Transact-SQL code or through your application:

12-2. Displaying the Oldest Active Transaction

Problem

USE AdventureWorks2014;
GO
BEGIN TRANSACTION
DELETE Production.ProductProductPhoto
WHERE ProductID = 317;

DBCC OPENTRAN('AdventureWorks2014');

(1 row(s) affected)
Transaction information for database 'AdventureWorks2014'.

Oldest active transaction:
SPID (server process ID): 54
UID (user ID) : -1
Name : user_transaction
LSN : (41:1021:39)
Start time : Dec 24 2014 12:45:53:780AM
SID : 0x010500000000000515000000a065cf7e784b9b5fe77c8770375a2900 DBCC execution completed. If DBCC printed error messages,
contact your system administrator.

Once you have this information, you can validate the Transact-SQL being executed using DMVs, figure out how long the process has been running, and, if necessary, shut down the process. DBCC OPENTRAN is useful for troubleshooting orphaned connections (connections still open in the database but disconnected from the application or client) and for identifying transactions missing a COMMIT or ROLLBACK statement.

This command also returns the oldest distributed and undistributed replicated transactions, if any exist within the database. If there are no active transactions, no session-level data will be returned.

This recipe demonstrates how to find out more information about an active transaction by querying the sys.dm_tran_session_transactions DMV. To demonstrate, I’ll describe a common scenario: Your application is encountering a significant number of blocks with a high duration. You’ve been told that this application always opens an explicit transaction prior to each query.

285