Get instant live expert help with SQL
“My expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Enter your problem description Our Experts are available now

Your message must be at least 40 characters
Select your SQL Environment:
Your message must be at least 40 characters
By submitting this form you agree to Got It's privacy policy.

What is the DBCC OPENTRAN command – Querychat

In this article, we will find out what is the DBCC OPENTRAN command in SQL Server and what its main purpose is. What is more, we will look at the examples with screenshots.

The DBCC OPENTRAN command syntax

When we work with the transaction log, we may end up having a problem when we understand that file sizes are constantly increasing, but we do not know why. Most likely there is an old open transaction somewhere in the depth of the journal, whose existence we have already forgotten.

To reduce the size of the log, SQL Server compresses it, removing data that has been stored longer than the oldest transaction in virtual logs. Only the inactive part of the log can be compressed; an open transaction can prevent the log from being compressed completely.

At this moment we can use the DBCC OPENTRAN command. This command helps to check if there any old SQL Server open transactions left in the log. 

It is important to note that we need to have proper permission to use this command. Namely, we need to have a fixed role, for e.g., a sysadmin role or a database owner role.

The syntax of the DBCC OPENTRAN command is:

DBCC OPENTRAN   

[   

    (DATABASE)   

    { [ WITH TABLERESULTS ]  

    }  

]

Where: 

  • DATABASE is the name of our database or its id where we want to show the open transactions. f we want to use the database we are using at the moment, we can write 0, or not specify it.
  • WITH TABLERESULTS is specified when we need to see our results in the form of a table. This can be convenient if we need to make a comparison of our results if we want to download these results in some other table. Otherwise, we can leave this unspecified, and the results will not be in the form of a table.

The DBCC OPENTRAN command example

Example 1. No active open transactions

In the beginning, let’s see what message we receive when there are no SQL Server open transactions in our current database.

Figure 1. No active transactions message

Example 2. Old open transactions

Now, let’s look at how the DBCC OPENTRAN command works when there are some old open transactions.

To see the oldest active transaction we have in our database, we will use the script: 

CREATE TABLE TestTableTran2(Test2 char(12));  

GO  

BEGIN TRAN  

INSERT INTO TestTableTran2 VALUES ('value1');  

GO  

DBCC OPENTRAN;  

ROLLBACK TRAN;  

GO  

DROP TABLE TestTableTran2;  

GO

Result:

Figure 2. DBCC OPENTRAN command example

It should be noted that different databases can have different result messages.

As we can see, the query showed us the  transaction we have in our current database.

Example 3. DBCC OPENTRAN command with TABLERESULTS option

Let’s look at our example again and apply the “with TABLERESULTS” parameter.

The parameter allows you to display information in a table form.

CREATE TABLE TestTableTran2(Test2 char(12));  

GO  

BEGIN TRAN  

INSERT INTO TestTableTran2 VALUES ('value1');  

GO  

DBCC OPENTRAN WITH TABLERESULTS;  

ROLLBACK TRAN;  

GO  

DROP TABLE TestTableTran2;  

GO

Result:

Figure 3. DBCC OPENTRAN WITH TABLERESULTS command example

Still need some help? Connect with a live SQLExpert here for some 1 on 1 help. Your first session is always free!

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc