Most of the time you run into a situation where your SQL server doesn't allow you to create a connection because of limit of the open connection at at time. This is indication that your application is leaking open connections. Some of your lazy developers forgot to close connection to database after use.
To debug this problem you need to know how many open connections your application has left in the connection pools.
There is a very simple and handy query which can help you out in such situations.
SELECT SPID,
STATUS,
PROGRAM_NAME,
LOGINAME=RTRIM(LOGINAME),
HOSTNAME,
CMD,
DBID
FROM MASTER.DBO.SYSPROCESSES
WHERE DBID != 0
If you know the exact database name then you can add Database filter in where clause to filter out connections made to specific database. see below.
AND DB_NAME(DBID) = 'EnterDBName'
Hope you find this query useful., cheers.
