Export (0) Print
Expand All
3 out of 3 rated this helpful - Rate this topic

DBCC OPENTRAN (Transact-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.

ms182792.note(en-US,SQL.90).gifNote:
DBCC OPENTRAN is not supported for non-SQL Server Publishers.

Topic link icon Transact-SQL Syntax Conventions


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

Is the name or ID of the database for which to display the oldest transaction information. If not specified, or if 0 is specified, the current database is used. Database names must comply with the rules for Identifiers.

WITH

Enables options to be specified.

TABLERESULTS

Specifies the 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.

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

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

Use DBCC OPENTRAN to determine whether an open transaction exists within the transaction log. When you use the BACKUP LOG statement, only the inactive part of the log can be truncated; an open transaction can prevent the log from truncating 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. To identify an open transaction, use sp_who to obtain the system process ID.

Requires membership in the sysadmin fixed server role or the db_owner fixed database role.

The following example obtains transaction information for the current database. Results may vary.

CREATE TABLE T1(Col1 int, Col2 char(3));
GO
BEGIN TRAN
INSERT INTO T1 VALUES (101, 'abc');
GO
DBCC OPENTRAN;
ROLLBACK TRAN;
GO
DROP TABLE T1;
GO

Here is the result set.

Transaction information for database 'master'.
Oldest active transaction:
SPID (server process ID) : 52
UID (user ID) : -1
Name          : user_transaction
LSN           : (518:1576:1)
Start time    : Jun  1 2004  3:30:07:197PM
SID           : 0x010500000000000515000000a065cf7e784b9b5fe77c87709e611500
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.