kyle.eldridge (9/23/2015)

---

Thanks, this is actually a very old revision which has since been revised.

please see the revised version:)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

USE [Master];

SELECT mdsd.Name AS 'Database Name'

, dmes.Host_Name AS 'Computer Name'

, NT_User_Name AS 'Windows Username'

, dmes.Session_ID AS 'Session ID'

, CASE WHEN spn.Open_Tran = '0' THEN 'There are no open transactions currently running on the ' + mdsd.Name + ' Database'

WHEN spn.Open_Tran = '1' THEN 'There is 1 open transaction currently running on the ' + mdsd.Name + ' Database'

WHEN spn.Open_Tran = '2' THEN 'There are 2 or more open transactions currently running on the ' + mdsd.Name + ' Database'

WHEN spn.Open_Tran >= '3' THEN 'There are 3 or more open transactions currently running on the ' + mdsd.Name + ' Database'

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 'Unspecified'

WHEN dmes.Transaction_Isolation_Level = '1' THEN 'Read Uncommitted'

WHEN dmes.Transaction_Isolation_Level = '2' THEN 'Read Committed'

WHEN dmes.Transaction_Isolation_Level = '3' THEN 'Repeatable Read'

WHEN dmes.Transaction_Isolation_Level = '4' THEN 'Serializable'

WHEN dmes.Transaction_Isolation_Level = '5' THEN 'Snapshot'

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].[dbo].[SysProcesses] spn

JOIN [sys].[DM_Exec_Sessions] 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'