How to monitor active connection pool in SQL Server?

Question

I'm suspecting that my web application has connection leaks (getting the timeout and max connection reached error). So I wanted to monitor how many database connections are active in the pool. I'm using the SQL Express so I don't have User Connections performance counter as suggested in some help guides.

I do found that I could also use Performance Monitor of Win 2008 server but I've no idea how to do so. Any guide on that would be appreciated.

Answer 1

Use simple

SELECT * FROM sys.dm_exec_connections 

OR

Try and adopt this script in case if you need some other data

declare @now    datetime set     @now    = getdate() set nocount off select  p.spid                      as  spid     ,   rtrim(p.loginame)           as  SQLUser     ,   rtrim(p.nt_username)        as  NTUser     ,   rtrim(p.nt_domain)          as  NTDomain     ,   rtrim(case         when p.blocked <> 0 then 'BLOCKED'         else p.status         end)                        as status     ,   case          when p.blocked is null or p.blocked = 0 then ''              else convert(varchar(10),p.blocked)         end                         as  BlockedBySpid     ,   rtrim(p.cmd)                as  CurrentCommand     ,   case when p.dbid = 0 then '' else rtrim(db_name(p.dbid)) end    as  DBName     ,   isnull(rtrim(p.program_name),'')        as  ProgramName     ,   cast( cast(p.waittype as int) as nvarchar(10)) as  CurrentWaitType     ,   p.waittime              as  CurrentWaitTime     ,   p.lastwaittype              as  LastWaitType     ,   rtrim(p.waitresource)       as  LastWaitResource     ,   p.open_tran                 as  OpenTransactionCnt     ,   p.cpu                       as  CPUTime     ,   convert(bigint, p.physical_io) as   DiskIO     ,   p.memusage                  as  MemoryUsage     ,   p.hostprocess               as  HostProcess     ,   rtrim(p.hostname)           as  HostName     ,   p.login_time                as  LoginTime     ,   p.last_batch                as  LastBatchTime     ,   p.net_address               as  NetAddress     ,   ltrim(rtrim(p.net_library)) as  NetLibrary     ,   case          when    lower(p.status) not in ('sleeping', 'background', 'dormant', 'suspended')          or      p.open_tran > 0         or      p.blocked   > 0         or      upper(ltrim(rtrim(p.cmd))) like 'WAITFOR%'         then    'Y'         else    'N'         end                         as  Active     ,   case         when    p.net_address <> '' --  Non system processes         and     p.program_name not like 'SQLAgent - %'         then 'N'         else 'Y'         end                         as  SystemProcess     ,   case          when p.last_batch = '19000101'      then 'n/a'         when datediff(day,      p.last_batch, @now) >   2   then convert(varchar(10),datediff(day,      p.last_batch, @now)) + ' days'         when datediff(hour,     p.last_batch, @now) >=  4   then convert(varchar(10),datediff(hour,     p.last_batch, @now)) + ' hrs'         when datediff(minute,   p.last_batch, @now) >=  10  then convert(varchar(10),datediff(minute,   p.last_batch, @now)) + ' min'         else convert(varchar(10),datediff(second, p.last_batch, @now)) + ' sec'         end                         as  TimeSinceLastBatch     ,   p.kpid                      as  InternalKPID     ,   case             when    (lower(p.status) in ('background', 'dormant')          and     p.open_tran <= 0         and     p.blocked   <= 0         and     upper(ltrim(rtrim(p.cmd))) not like 'WAITFOR%'         ) or (         lower(p.status) like '%sleeping%'         )         then    0         else    p.kpid         end                         as  kpid     , (convert(nvarchar,p.spid) + '.' + case                                             when    (lower(p.status) in ('background', 'dormant')                                          and     p.open_tran <= 0                                         and     p.blocked   <= 0                                         and     upper(ltrim(rtrim(p.cmd))) not like 'WAITFOR%'                                         ) or (                                         lower(p.status) like '%sleeping%'                                         )                                         then    '0'                                         else    convert(nvarchar,p.kpid)                                         end) + '.' + convert(nvarchar,convert(float, p.login_time)) as SessionLifeTimeKey                     ,   convert(float, p.login_time) as 'LoginTimeFloatDiff' from    sys.sysprocesses            p   with (readpast)