How to determine total number of open active connections in ms sql server 2005

Question

My PHP MS Sql Server 2005 win 2003  Application occasionally becomes very unresponsive  the memory cpu usage does not spike  If i try to open any new connection from sql management studio  then the it just hangs at the open connection dialog box   how to deterime the total number of active connections ms sql server 2005

User · Accepted Answer

This shows the number of connections per each DB:

SELECT 
    DB_NAME(dbid) as DBName, 
    COUNT(dbid) as NumberOfConnections,
    loginame as LoginName
FROM
    sys.sysprocesses
WHERE 
    dbid > 0
GROUP BY 
    dbid, loginame

And this gives the total:

SELECT 
    COUNT(dbid) as TotalConnections
FROM
    sys.sysprocesses
WHERE 
    dbid > 0

If you need more detail, run:

sp_who2 'Active'

Note: The SQL Server account used needs the 'sysadmin' role (otherwise it will just show a single row and a count of 1 as the result)

User · Answer

As  jwalkerjr mentioned  you should be disposing of connections in code  if connection pooling is enabled  they are just returned to the connection pool   The prescribed way to do this is using the  using  statement        Execute stored proc to read data from repository using  SqlConnection conn   new SqlConnection this connectionString         using  SqlCommand cmd   conn CreateCommand                  cmd CommandText    LoadFromRepository           cmd CommandType   CommandType StoredProcedure          cmd Parameters AddWithValue   ID   fileID            conn Open            using  SqlDataReader rdr   cmd ExecuteReader CommandBehavior CloseConnection                         if  rdr Read                                  filename   SaveToFileSystem rdr  folderfilepath

User · Answer

If your PHP app is holding open many SQL Server connections  then  as you may know  you have a problem with your app s database code  It should be releasing disposing those connections after use and using connection pooling  Have a look here for a decent article on the topic     http   www c-sharpcorner com UploadFile dsdaf ConnPooling07262006093645AM ConnPooling aspx

User · Answer

If your PHP app is holding open many SQL Server connections  then  as you may know  you have a problem with your app s database code  It should be releasing disposing those connections after use and using connection pooling  Have a look here for a decent article on the topic     http   www c-sharpcorner com UploadFile dsdaf ConnPooling07262006093645AM ConnPooling aspx

User · Answer

SELECT  DATABASE    DB NAME DBID    OPNEDCONNECTIONS  COUNT DBID    USER   LOGINAME FROM SYS SYSPROCESSES GROUP BY DBID  LOGINAME ORDER BY DB NAME DBID   LOGINAME

User · Answer

As  jwalkerjr mentioned  you should be disposing of connections in code  if connection pooling is enabled  they are just returned to the connection pool   The prescribed way to do this is using the  using  statement        Execute stored proc to read data from repository using  SqlConnection conn   new SqlConnection this connectionString         using  SqlCommand cmd   conn CreateCommand                  cmd CommandText    LoadFromRepository           cmd CommandType   CommandType StoredProcedure          cmd Parameters AddWithValue   ID   fileID            conn Open            using  SqlDataReader rdr   cmd ExecuteReader CommandBehavior CloseConnection                         if  rdr Read                                  filename   SaveToFileSystem rdr  folderfilepath

User · Answer

I know this is old  but thought it would be a good idea to update   If an accurate count is needed  then column ECID should probably be filtered as well    A SPID with parallel threads can show up multiple times in sysprocesses and filtering ECID 0 will return the primary thread for each SPID   SELECT      DB NAME dbid  as DBName       COUNT dbid  as NumberOfConnections      loginame as LoginName FROM     sys sysprocesses with  nolock  WHERE      dbid  gt  0     and ecid 0 GROUP BY      dbid  loginame

User · Answer

As  jwalkerjr mentioned  you should be disposing of connections in code  if connection pooling is enabled  they are just returned to the connection pool   The prescribed way to do this is using the  using  statement        Execute stored proc to read data from repository using  SqlConnection conn   new SqlConnection this connectionString         using  SqlCommand cmd   conn CreateCommand                  cmd CommandText    LoadFromRepository           cmd CommandType   CommandType StoredProcedure          cmd Parameters AddWithValue   ID   fileID            conn Open            using  SqlDataReader rdr   cmd ExecuteReader CommandBehavior CloseConnection                         if  rdr Read                                  filename   SaveToFileSystem rdr  folderfilepath

User · Answer

I know this is old  but thought it would be a good idea to update   If an accurate count is needed  then column ECID should probably be filtered as well    A SPID with parallel threads can show up multiple times in sysprocesses and filtering ECID 0 will return the primary thread for each SPID   SELECT      DB NAME dbid  as DBName       COUNT dbid  as NumberOfConnections      loginame as LoginName FROM     sys sysprocesses with  nolock  WHERE      dbid  gt  0     and ecid 0 GROUP BY      dbid  loginame

User · Answer

Use this to get an accurate count for each connection pool  assuming each user host process uses the same connection string   SELECT  DB NAME dbid  as DBName   COUNT dbid  as NumberOfConnections  loginame as LoginName  hostname  hostprocess FROM sys sysprocesses with  nolock  WHERE  dbid  gt  0 GROUP BY  dbid  loginame  hostname  hostprocess

User · Answer

Use this to get an accurate count for each connection pool  assuming each user host process uses the same connection string   SELECT  DB NAME dbid  as DBName   COUNT dbid  as NumberOfConnections  loginame as LoginName  hostname  hostprocess FROM sys sysprocesses with  nolock  WHERE  dbid  gt  0 GROUP BY  dbid  loginame  hostname  hostprocess

User · Answer

If your PHP app is holding open many SQL Server connections  then  as you may know  you have a problem with your app s database code  It should be releasing disposing those connections after use and using connection pooling  Have a look here for a decent article on the topic     http   www c-sharpcorner com UploadFile dsdaf ConnPooling07262006093645AM ConnPooling aspx

User · Answer

As  jwalkerjr mentioned  you should be disposing of connections in code  if connection pooling is enabled  they are just returned to the connection pool   The prescribed way to do this is using the  using  statement        Execute stored proc to read data from repository using  SqlConnection conn   new SqlConnection this connectionString         using  SqlCommand cmd   conn CreateCommand                  cmd CommandText    LoadFromRepository           cmd CommandType   CommandType StoredProcedure          cmd Parameters AddWithValue   ID   fileID            conn Open            using  SqlDataReader rdr   cmd ExecuteReader CommandBehavior CloseConnection                         if  rdr Read                                  filename   SaveToFileSystem rdr  folderfilepath

User · Answer

MS SQL knowledge based - How to know open SQL database connection s  and occupied on which host   Using below query you will find list database  Host name and total number of open connection count  based on that you will have idea  which host has occupied SQL connection    SELECT DB NAME dbid  as DBName  hostname  COUNT dbid  as NumberOfConnections FROM sys sysprocesses with  nolock   WHERE dbid  gt  0  and len hostname   gt  0  --and DB NAME dbid   master     Open this line to filter Database by Name    Group by DB NAME dbid  hostname order by DBName

User · Answer

see sp who it gives you more details than just seeing the number of connections   in your case i would do something like this    DECLARE  temp TABLE spid int   ecid int  status varchar 50                        loginname varchar 50                           hostname varchar 50   blk varchar 50   dbname varchar 50   cmd varchar 50   request id int   INSERT INTO  temp    EXEC sp who  SELECT COUNT    FROM  temp WHERE dbname    DB NAME

User · Answer

see sp who it gives you more details than just seeing the number of connections   in your case i would do something like this    DECLARE  temp TABLE spid int   ecid int  status varchar 50                        loginname varchar 50                           hostname varchar 50   blk varchar 50   dbname varchar 50   cmd varchar 50   request id int   INSERT INTO  temp    EXEC sp who  SELECT COUNT    FROM  temp WHERE dbname    DB NAME

User · Answer

SELECT  DATABASE    DB NAME DBID    OPNEDCONNECTIONS  COUNT DBID    USER   LOGINAME FROM SYS SYSPROCESSES GROUP BY DBID  LOGINAME ORDER BY DB NAME DBID   LOGINAME

User · Answer

If your PHP app is holding open many SQL Server connections  then  as you may know  you have a problem with your app s database code  It should be releasing disposing those connections after use and using connection pooling  Have a look here for a decent article on the topic     http   www c-sharpcorner com UploadFile dsdaf ConnPooling07262006093645AM ConnPooling aspx

User · Answer

MS SQL knowledge based - How to know open SQL database connection s  and occupied on which host   Using below query you will find list database  Host name and total number of open connection count  based on that you will have idea  which host has occupied SQL connection    SELECT DB NAME dbid  as DBName  hostname  COUNT dbid  as NumberOfConnections FROM sys sysprocesses with  nolock   WHERE dbid  gt  0  and len hostname   gt  0  --and DB NAME dbid   master     Open this line to filter Database by Name    Group by DB NAME dbid  hostname order by DBName

[sql-server] How to determine total number of open/active connections in ms sql server 2005

Examples related to sql-server

Examples related to database

Examples related to sql-server-2005

Examples related to connection