Monday, March 9, 2009

SQL server open connections exist in a database

If you get a “timeout connection in the database because there are no connections available in the connection pool” error, the following queries will help you diagnose the problem:

  • To allow users to view current activity on the database:
    sp_who2
  • To give you the total number of connections per database on a database server:
    SELECT DB_NAME(dbid) as 'Database Name',
    COUNT(dbid) as 'Total Connections'
    FROM master.dbo.sysprocesses WITH (nolock)
    WHERE dbid > 0
    GROUP BY dbid
  • To get the dbid from database name
    SELECT DB_ID('MyDBName') as [Database ID]
  • To give you the process Ids of existing connections in the database (not necessarily open but existing):
    SELECT spid
    FROM master.dbo.sysprocesses WITH (nolock)
    WHERE dbid = (SELECT DB_ID('MyDBName') as [Database ID])
  • To give you information about the actual process id (replace 1018 with the spid):
    dbcc inputbuffer (1018)
  • To kill a process
    kill 1018

Thanks Tracey.

blog comments powered by Disqus