DBCC OPENTRAN (T-SQL)

Displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the specified database. Results are displayed only if there is an active transaction or if the database contains replication information. An informational message is displayed if there are no active transactions.

Syntax

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

Arguments

'database_name'

Is the name of the database for which to display the oldest transaction information. Database names must conform to the rules for identifiers. For more information, see Using Identifiers.

database_id

Is the database identification number (ID) for which to display the oldest transaction information. Obtain the database ID by using the DB_ID function.

WITH TABLERESULTS

Specifies results in a tabular format that can be loaded into a table. Use this option to create a table of results that can be inserted into a table for comparisons. When this option is not specified, results are formatted for readability.

NO_INFOMSGS

Suppresses all informational messages.

Remarks

If neither database_name nor database_id is specified, the default is the current database.

Use DBCC OPENTRAN to determine whether an open transaction exists within the log. When using the BACKUP LOG statement, only the inactive portion of the log can be truncated; an open transaction can cause the log to not truncate completely. In earlier versions of Microsoft® SQL Server™, either all users had to log off or the server had to be shut down and restarted to clear uncommitted transactions from the log. With DBCC OPENTRAN, an open transaction can be identified (by obtaining the system process ID from the sp_who output) and terminated, if necessary.

Result Sets

DBCC OPENTRAN returns this result set when there are no open transactions:

No active open transactions.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Permissions

DBCC OPENTRAN permissions default to members of the sysadmin fixed server role or the db_owner fixed database role, and are not transferable.

Examples

A. Show open transactions both in the current database and in the pubs database

This example obtains transaction information for the current database and for the pubs database.

-- Display transaction information only for the current database.

DBCC OPENTRAN

GO

-- Display transaction information for the pubs database.

DBCC OPENTRAN('pubs')

GO

B. Show open transactions in a tabular format and load into a table

This example obtains the transaction information in a table form, and then loads the information into a SQL Server table.

-- Generate the output in table form.

DBCC OPENTRAN('pubs') WITH TABLERESULTS

GO

-- Put transaction information into the table

DECLARE @string VARCHAR (255)

SELECT @string   = "DBCC OPENTRAN('pubs') WITH TABLERESULTS"

SELECT @string

-- Create the table.

CREATE TABLE #opentran_output

(trantag varchar(30), tranvalue varchar(46))

-- Use INSERT INTO...EXEC to load into the table.

INSERT INTO #opentran_output

EXEC (@string)

GO

--Display the results.

SELECT *

FROM #opentran_output

GO

See Also