DB - MS-SQL -> SQL user connections

Monitoring user connections in SQL Server can be done using several methods. This section queries the systemview to get the user connections for each database.


USE MASTER
GO

DECLARE @DML nvarchar(MAX)
DECLARE @Connections_GRV TABLE
(
DBName [nvarchar](128) NULL,
No_Of_Connections [int] NULL
)

SET @DML='
SELECT DB_NAME(dbid) DBName,COUNT(*) No_Of_Connections FROM sys.sysprocesses --where kpid>0
group by DB_NAME(dbid)
ORDER BY DB_NAME(dbid) DESC OPTION (RECOMPILE)
'

INSERT INTO @Connections_GRV
EXEC sp_executesql @DML


select * from @Connections_GRV