Find Open Transactions Across All Databases Using DBCC OpenTran(‘MyDB’)

Here’s a quick script you can run to see if there are any open transactions across any of your databases. Sure; there are a number of ways to see this activity, but this might be helpful for something to build on later.

use master;

set nocount on
declare @check_for_open_transactions varchar(max)
set @check_for_open_transactions = ”
select @check_for_open_transactions = @check_for_open_transactions +

dbcc opentran(”’ + name + ”’); ‘ + char(10)
from sys.databases where name not in (‘master’, ‘model’, ‘tempdb’, ‘msdb’)
order by database_id asc
exec (@check_for_open_transactions)

Just browse the results. You’ll see the transactions readily.


Categories: Security, SQL Stuff, TSQL

Tagged as: , ,

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s