DBA - How To Find Open Transactions In SQL Server

As SQL Server DBA or Developer we often face issues such as blocking etc. where we have to find open transactions.

In our step by step approach , we are going to open two transactions as shown below. The both transactions are update transactions which are updating records in different tables in different Databases.
Fig 1: Updating dbo.Country Table in TestDB

Fig 2: Updating dbo.Customer Table in Test1 Database

Let's run both of the queries. Now both transactions are open as we did not commit them. We can use DBCC OPENTRAN to find out open transactions.

Fig 3: Using DBCC OPENTRAN to find out open transactions in SQL Server

We know that we have two open transactions but DBCC OPENTRAN is only showing us one transaction. Well, DBCC OPENTRAN only shows open transactions for specific database for which it is executed. Here it is showing us only open transactions in TEST1 Database. 

Now we know the SPID ( server process ID) , we want to know which query is associated with this process id. We can use DBCC INPUTBUFFER(SPID) to get the query information as shown below

Fig 4: Using DBCC INPUTBUFFER to get Query from SPID (Process ID) in SQL Server

DBCC OPENTRAN AND DBCC INPUTBUFFER are very helpful but If we have more than one transactions open and we want to get all information in once, which query we can use to get all open transactions with database name,who executed these queries,sql query and program name etc.

This query can be used to get all above information

USE MASTER
GO
SELECT spid,
       PROGRAM_NAME,
       nt_userName,
       loginame,
       DB_NAME(s.dbid) AS DatabaseName,
       CR.TEXT AS Query
FROM   sysprocesses s
       CROSS apply sys.Dm_exec_sql_text(sql_handle) CR
WHERE  open_tran = 1

By using Sysprocesses we don't have to query one database at a time to find out open transactions, it will return us all open transactions on SQL Server.
Fig 5: Using Sysprocesses to get all Open Transactions in SQL server with Query information









No comments:

Post a Comment