HOW TO IDENTIFY AN OPEN TRANSACTION IN A DATABASE??

The fastest way to identify long-running transaction is to use DBCC OPENTRAN. This command can accept the database name as an input parameter in the format DBCC OPENTRAN(DatabaseName) where DatabaseName is the name of the database to check for open transactions.

If an active transaction exists in the database, this command will output information similar to the following:
========================================================================
Transaction information for database 'Test'.
Oldest active transaction:
    SPID (server process ID): 66
    UID (user ID) : -1
    Name          : user_transaction
    LSN           : (1348:2852:1)
    Start time    : Aug 11 2014  3:24:25:487PM
    SID           : 0x01
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
========================================================================

We can also get the information about open transaction from sys.sysprocesses as follows:

select * from sys.sysprocesses where open_tran > 0

Let see the same with help of a sample database "Test".

In this "TEST" database, We have a table as T with column ID and name, Now We'll use the below code which will take time to get execute as We have mentioned "WAITFOR DELAY '00:50:50'. 

BEGIN TRAN
SELECT 'TEST'
INSERT INTO A VALUES(13,'VI')
WAITFOR DELAY  '00:50:50'
COMMIT TRAN

Now we will execute DBCC OPENTRAN() Command to see the information about open transactions.

When we will use sys.sysprocesses we will get the information as below:

Now, We'll use below script to find the actual query which is taking time:

SELECT      r.start_time [Start Time],session_ID [SPID],
            DB_NAME(database_id) [Database],
            SUBSTRING(t.text,(r.statement_start_offset/2)+1,
            CASE WHEN statement_end_offset=-1 OR statement_end_offset=0 
            THEN (DATALENGTH(t.Text)-r.statement_start_offset/2)+1 
            ELSE (r.statement_end_offset-r.statement_start_offset)/2+1
            END) [Executing SQL], 
            Status,command,wait_type,wait_time,wait_resource, 
            last_wait_type
FROM        sys.dm_exec_requests r
OUTER APPLY sys.dm_exec_sql_text(sql_handle) t
WHERE       session_id =66 --Passed the session Id which We have got from DBCC opentran()
ORDER BY    r.start_time

After execution we will get the output as below:

From here we can see the current executing query is "WAITFOR DELAY".

If you want to see the whole batch of code then simply execute the below code:

SELECT s.session_id ,
s.status ,
s.host_name ,
s.program_name ,
s.login_name ,
s.login_time ,
s.last_request_start_time ,
s.last_request_end_time ,
t.text
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
WHERE s.session_id = 66 

We will get output as Below:

See the "text" column in results..It is having all TSQL statements. So, this is how we can get the information about all open transactions in a database.

Thanks For Reading This Post!!!!