To look for long-running transactions, use one of the following:

• sys.dm_tran_database_transactions
  This dynamic management view returns information about transactions at the database level. For a long-running transaction, columns of particular interest include the time of the first log record (database_transaction_begin_time), the current state of the transaction (database_transaction_state), and the log sequence number (LSN) of the begin record in the transaction log (database_transaction_begin_lsn).
  For more information, see sys.dm_tran_database_transactions (Transact-SQL).
  
• DBCC OPENTRAN
  This statement lets you identify the user ID of the owner of the transaction, so you can potentially track down the source of the transaction for a more orderly termination (committing it rather than rolling it back). For more information, see DBCC OPENTRAN (Transact-SQL).
  

Note:
For information about other factors that can delay log truncation, see Factors That Can Delay Log Truncation.

You may have to use the KILL statement. Use this statement very carefully, however, especially when critical processes are running. For more information, see KILL (Transact-SQL).

Other Resources

Help and Information