January 29, 2013 at 8:28 pm
Comments posted to this topic are about the item Open transactions on a database
September 23, 2015 at 6:24 am
Excellent, thanks for the script.
September 23, 2015 at 6:43 am
DECLARE @databasename sysname
is sufficient. nvarchar(max) looks a little bit overwhelmed
September 23, 2015 at 7:12 am
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'
September 23, 2015 at 7:42 am
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'
Nice script and thanks for the update. I would like to ask why you have WHERE dmes.session_id = spn.spid in the where statement since it is in the JOIN of those two tables?
Also have you thought about using a CTE to hold a table of database names to run the query against?
September 23, 2015 at 7:46 am
Hi,
Not sure why the Where is in there, thought I had removed that already, obviously not!
a CTE would be fine to hold the table names, but this runs just fine as it is, I feel a CTE would overcomplicate things.
I have run this on a server with over 180 databases and it returns instantly each time, if you wanted to limit what servers to query then you can always add it into a where clause, not sure what other use a CTE would have.
appreciate the feedback and suggestion also 🙂
September 23, 2015 at 9:40 am
Nice script. Three(minor) comments:
First, ** While running the below query if you find any query which ** is repeated in the comment header.
Second, I don't understand why you have WHEN [spn].[open_tran] = 2 THEN, couldn't you remove that and change
WHEN [spn].[open_tran] >= 3 THEN
to
WHEN [spn].[open_tran] >= 2 THEN?
Third, the case below will never happen
CASE WHEN [spn].[open_tran] = 0
because of the filter in the WHERE clause:
AND [spn].[open_tran] <> 0;
thanks
September 23, 2015 at 10:23 am
kyle.eldridge (9/23/2015)
Hi,Not sure why the where is in there, thought I had removed that already, obviously not!
a CTE would be fine to hold the table names, but this runs just fine as it is, I feel a CTE would overcomplicate things.
I have run this on a server with over 180 databases and it returns instantly each time, if you wanted to limit what servers to query then you can always add it into a where clause, not sure what other use a CTE would have.
appreciate the feedback and suggestion also 🙂
You don't run it against 180 databases at the same time with the script you have posted here.
Not unless you are executing the script 180 times which would never be instantaneous. or removed the database name from the where clause which would not be User databases only.
However that does sound like a better idea for a troubleshooting tool.
Just because I am logged into database Sales does not mean I cant open a transaction that affects data in the Accounting database.
April 28, 2016 at 7:20 am
Thanks for the update.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply