SQL Server Performance

Commit and Rollback Counters

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by DanielM, Feb 5, 2009.

  1. DanielM New Member

    Is there an internal counter that tracks the number of transactions that have been commited or rolled back?
  2. Elisabeth Redei New Member

    Hi Daniel,
    There is a Performance Monitor counter, SQL Server:Databases/Transactions/sec that shows you current transactions in the database (whether rolled back or commited). You can get this number by querying the sys.dm_os_performance_counters system view but neither of these are going to give you an accumulated number for some period in time.
    Another way is to use Profiler to capture incoming traffic for the period you are interested in. If you include the Transaction:Transaction Log event, the EventSubclass will tell you whether it was a COMMIT or a ROLLBACK.
    If you want numbers for COMMIT and ROLLBACK in your current transaction log, you could issue a query that looks something like this:SELECT COUNT
    (*) AS TxCount, Operation FROM fn_dblog(NULL, NULL) WHERE
    operation = 'LOP_COMMIT_XACT' OR operation = 'LOP_ABORT_XACT'GROUP BY
    operation
    HTH
    /Elisabeth

Share This Page