Friday, 15 February 2013

Open transactions on a database

The slowness was caused by a transaction which was undetected due to a number of query windows being open.

this script will show you the following:

  • Computer/Host Name
  • Windows Username
  • Database which the transaction is being run from
  • Session ID
  • Number of open transactions
  • Date the query window was first opened.
  • If the query window is a user process or not
  • The transaction isolation level
  • Lock timeout of the query.



/*************************************************************************
The following script will show you If there are any open transactions 
running on your database. While running the below query if you find any 
query which is running for long time it can be killed using following 
command:
KILL [session_id] 
you could also open the query window the transaction is open on and use 
the following command:
RollBack Transaction
*************************************************************************/


SELECT DMES.host_name AS 'Computer Name'
, nt_user_name AS 'Windows Username'
, mdsd.NAME AS 'Database with open Transaction'
, DMES.session_id AS 'Session ID'
, CASE 
WHEN spn.open_tran = 0
THEN 'There are no open transactions currently running'
WHEN spn.open_tran = 1
THEN 'There is 1 open transaction currently running'
WHEN spn.open_tran = 2
THEN 'There are 2 or more open transactions currently running'
WHEN spn.open_tran >= 3
THEN 'There are 3 or more open transactions currently running'
END AS 'Open Transactions'
, RTRIM(CAST(DMES.login_time AS NVARCHAR(30))) AS 'Date The Query window opened'
, CASE 
WHEN DMES.is_user_process = 0
THEN 'No'
WHEN DMES.is_user_process = 1
THEN 'Yes'
END AS 'User Process'
, CASE 
WHEN DMES.transaction_isolation_level = 0
THEN 'Read Uncommitted'
WHEN DMES.transaction_isolation_level = 1
THEN 'Read Committed'
WHEN DMES.transaction_isolation_level = 2
THEN 'Repeatable Read'
WHEN DMES.transaction_isolation_level = 3
THEN 'Serializable Reads'
END AS 'Transaction Isolation Level'
, CASE 
WHEN DMES.LOCK_TIMEOUT = '-1'
THEN 'No lock time out specified, the lock will expire when the transaction has completed'
WHEN DMES.LOCK_TIMEOUT >= 0
THEN ' A Lockout Time of' + ' ' + CAST(CONVERT(REAL, (DMES.LOCK_TIMEOUT) / (1000.00)) AS VARCHAR(MAX)) + ' ' + 'Seconds has been specified'
END AS 'Lock Timeout'
FROM master..sysprocesses AS spn
JOIN sys.dm_exec_sessions AS DMES
ON DMES.session_id = spn.spid
JOIN master.dbo.sysdatabases mdsd
ON spn.dbid = mdsd.dbid
WHERE DMES.session_id = spn.spid
AND spn.open_tran <> 0

No comments:

Post a Comment