SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Open transactions on a database


Open transactions on a database

Author
Message
kyle.eldridge
kyle.eldridge
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 28
Comments posted to this topic are about the item Open transactions on a database
Iwas Bornready
Iwas Bornready
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15986 Visits: 885
Excellent, thanks for the script.
samot-dwarf
samot-dwarf
Old Hand
Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)

Group: General Forum Members
Points: 355 Visits: 141
DECLARE @databasename sysname
is sufficient. nvarchar(max) looks a little bit overwhelmed
kyle.eldridge
kyle.eldridge
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 28
Thanks, this is actually a very old revision which has since been revised.

please see the revised versionSmile

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'
PHYData DBA
PHYData DBA
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1263 Visits: 537
kyle.eldridge (9/23/2015)
Thanks, this is actually a very old revision which has since been revised.

please see the revised versionSmile

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?
kyle.eldridge
kyle.eldridge
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 28
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 Smile
jeffgonnering
jeffgonnering
Old Hand
Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)

Group: General Forum Members
Points: 355 Visits: 531
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
PHYData DBA
PHYData DBA
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1263 Visits: 537
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 Smile


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.
Iwas Bornready
Iwas Bornready
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15986 Visits: 885
Thanks for the update.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search