Check Active Transactions In SQL Server – DBCC OPENTRAN
In SQL Server, transactions are the essential components to trace the database logs. The SQL Server executes all its Statements through the transaction logs or SQL Server active transactions. After executing the statement the transaction log contains specific information about the committed transactions. In this article, we will learn how to check active transaction in SQL server with some T-SQL commands.
Table of Content
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 to check active transaction in SQL Server 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 to check SQL server active transactions:
DBCC OPENTRAN
[
( [ database_name | database_id | 0 ] ) ]
{ [ WITH TABLERESULTS ]
[ , [ NO_INFOMSGS ] ]
}
]
Argument
Database_Name | Database_Id | 0
The database name or ID for which the system shows the oldest transaction data. In case the current database is used, then the ID should either be 0 or not specified. Also, users must note that the database names must comply with the rules for identifiers. To check SQL Server active transactions using OPENTRAN command, users must be aware of this.
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. It makes it easy for users to know how to check active transaction in sql server without any errors.
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 to display active transactions SQL Server:
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
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. It can be really helpful to check SQL server active transactions for sure. 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. Also, the program helps you to recognize the queries of particular transactions according to the priority implemented. Now, we can say that it’s easy to check active transaction in SQL server database.