Get all Database Active Connections and Transactions in TSQL

At times we might need to check out all active connections made by all processes to the SQL Server databases. Also some times we might need to check with all active transactions in the SQL Engine. To accomplish the same tasks, we have two system provided views in master database. To get processes, use ‘sys.sysprocesses’. To get transactions, use ‘dm_tran_active_transactions’.

Please check below code –

/************************** List all Processes ********************************
We need to query sysprocesses system view to get all the processes running in 
SQL Server instance.
Check for [dbid] not zero, for getting all connections to all databases and not
including those of system processes.
*******************************************************************************/

SELECT 
        [dbid],
        DB_NAME([dbid]) as DatabaseName, 
        COUNT([dbid]) as ConnectionsCount,
        [loginame] as LoginName,
        [status]
FROM
        [master].[sys].[sysprocesses]
WHERE
        [dbid] > 0
GROUP BY 
        [dbid], [loginame], [status];


/************************ List all Active Transactions ************************
We need to query dm_tran_active_transactions system view to get all the active 
transactions running in SQL Server instance.
*******************************************************************************/

SELECT 
        [transaction_id] AS TranId,
        [name] AS TranName,
        [transaction_begin_time] AS TranStartTime,
        [transaction_state] AS TranState,
        [transaction_status] AS TranStatus
FROM 
        [master].[sys].[dm_tran_active_transactions];

 

Output –

ActiveTransAndPRocesses

You may also like...