DBCC OPENTRAN
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 (ID) number for which to display the oldest transaction information. Obtain the database ID 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
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