Open transactions on a database

  • kyle.eldridge

    SSC Rookie

    Points: 47

    Comments posted to this topic are about the item Open transactions on a database

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Excellent, thanks for the script.

  • samot-dwarf

    SSC Eights!

    Points: 984

    DECLARE @databasename sysname

    is sufficient. nvarchar(max) looks a little bit overwhelmed

  • kyle.eldridge

    SSC Rookie

    Points: 47

    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'

  • PHYData DBA

    SSCertifiable

    Points: 7541

    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?

  • kyle.eldridge

    SSC Rookie

    Points: 47

    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 🙂

  • jeffgonnering

    SSChasing Mays

    Points: 614

    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

    SSCertifiable

    Points: 7541

    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.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the update.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply