Check Active Transactions In SQL Server – DBCC OPENTRAN

Andrew Jackson | December 1st, 2018 | SQL Transaction Log |

Introduction

In SQL Server, transactions are the essential components to trace the database logs. The SQL Server executes all its Statements through the transaction logs. After executing the statement the transaction log contains a specific information about the committed transactions. In this article, we will know how to check active transactions in SQL Server with some T-SQL commands.

What are the Active Transactions in SQL Server?

An active transaction in SQL Server is a transaction which is currently being executed on the Server. Checking active transactions in SQL Server are helpful to verify the effectiveness of the database. The number of active transactions tells, there are certain transactions that are never completing. No other transaction can change the data which is held by the active transaction throughout an active transaction. While we are changing the data, we hold a unique lock on that data. Conversely, we are unable to read that data held by another transaction because another transaction holds a special lock on the data which stops you from analyzing it. Maintaining a low level of active transactions helps in maintaining the request for data accessibility. There are two different categories of methods with which you can explore and view SQL Server transaction logs.

Reading Transactional Activity Through DMV’s

There are three Dynamic Management Views which permits users to check active transactions in SQL Server as well as those transactions that are the source of the transaction log to grow quickly.

1. sys.dm_tran_session_transactions: This is the main transactional view which allows users to connect to the sys.dm_exec DMVs with the other sys.dm_tran_*DMVs

2. sys.dm_tran_active_transactions: This function stores the transactional information relating to situation, type, and position of each operation presently initiated but not yet accomplished, on the SQL instance. It also gives information on allocated transactions through some of the DTC-centric columns has been deprecated in SQL Server 2008.

3. sys.dm_tran_database_transactions: The function stores much of the similar information in regard to transaction state, rank, and type; but also gives extremely grainy transaction log metrics and record count and size metadata.

Using both sys.dm_tran_database_transactions and sys.dm_tran_active_transactions, we get information on the duration and the status of our users’ transactions, and monitored the physical effects of those transactions on the database transaction log files on disk.

Check Active Transaction in SQL Server using DBCC OPENTRAN

Previous to SQL Server 2005, nearly all DBAs used the DBCC OPENTRAN command to get back information about presently open transactions on their SQL Server instance. So we still use DBCC OPENTRAN for the same reason that it was designed for: to give information regarding the oldest transaction, presently active in a specific database.

The DBCC OPENTRAN helps to check active transactions in SQL Server that may avoid log truncation. DBCC OPENTRAN shows information about the previously active transaction and the previously distributed and non distributed copied transactions, if any, within the transaction log of the specific database. Results are shown only if there is an active transaction that exists in the log or if the database holds copied information. An informational message is displayed if no active transaction exists in the log.

You can apply the following syntax:

DBCC OPENTRAN 
[ 
    ( [ database_name | database_id | 0 ] ) ]
    { [ WITH TABLERESULTS ]
      [ , [ NO_INFOMSGS ] ]
    }
] 

Argument

database_name | database_id| 0

The database name and the database ID are the fields of the database for displaying the oldest transaction information. If it is not in detail, or if it is 0; the current database is being used. It is necessary that the database names should follow the rules for identifiers.

TABLERESULTS

The TABLERESULTS define the results in a tabular format that can be overloaded into a table. The option used in the creation of results table that can be put into a table for comparison. If this option is not defined, the results are formatted for readability.

NO_INFOMSGS

This compresses all informational messages.

Use DBCC OPENTRAN to verify whether an open transaction exists within the transaction log. The inactive part of the log can be truncated if you use the BACKUP LOG statement, an open transaction can avoid the log from truncating entirely. To recognize an open transaction, use sp_who to get the system process ID.

The following example shows how the DBCC OPENTRAN works:

CREATE TABLE trans(Column1 int, Column2 char(3));
GO
BEGIN TRANSACTION
INSERT INTO trans VALUES (1, 'abc');
GO
DBCC OPENTRAN;
ROLLBACK TRANSACTION;
GO
DROP TABLE trans;
GO

Capture44

we can see how the DBCC OPENTRAN executes and shows the old active transactions in SQL Server.

Conclusion

The DBCC OPENTRAN is a useful command of Transact- SQL which helps to explore previously running transactions for the specific database. The DBCC OPENTRAN returns the oldest active transactions and if there are no active transactions in SQL Server; the DBCC OPENTRAN returns the following result:

No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator

This DBCC OPENTRAN gives the overview only. Therefore, to know the data related transactional details the use of SQL Server Log Explorer is beneficial. The program helps you to recognize the queries of particular transactions according to the priority implemented.

Useful Links

Become a Writer
Do you have flair of writing for SQL Server & other Database related Technology
We welcome the technical post from bloggers & technical writers who can contribute interesting stories about SQL Server on regular basis. If you think you are a good fit, then this SQL Server Explorer is a Platform for you. Stay in Touch with us.