July 14, 2008 at 8:46 am
I'm getting an error message from an asp.net page saying that max pooled connections are in use. I know that the cause of this is most likely an unclosed connection, but I was doing a little looking into it and was using sp_who to show me how many users are connected.
The problem is, this is listing all databases on the instance, not the particular database I need
Then I found this script which I've had for a while
select COUNT(*)
from master..sysprocesses
where db_name(dbid) = 'myDbName'
Which was returning 60 connections.. so I altered the script a little
select loginame, status, count(*)
from master..sysprocesses
where db_name(dbid) = 'myDbName'
GROUP BY loginame, status
which gave:
loginame status (No column name)
sa background 2
time dormant 47
domain\username runnable 1
domain\username1 sleeping 6
sa sleeping 2
I was confused by the entry for time, so I tracked it down in the Security in Enterprise Manager..
Its set to be a system administrator with database access to a different database on the instance..
not sure why its showing in my script as connected to MY database, and I'm worried that this is whats causing my issue.
can anyone give me some direction?
Thanks
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
July 14, 2008 at 11:24 am
I believe you need to fix this dormant connections.
Can you cap the number of connections for “time”? If so, it will allow you to restrict “time” to open many connections. Please refer to http://home.consultdba.com/kbhome3232 for script to do this.
Please let me know if this is not an affordable option.
July 14, 2008 at 11:27 am
thanks for the suggestion, but im not allowed to touch the user 'time'
i've upped the max connections via the connection string, hopefully that'll hide the problem.. i think the solution to my problem here might be moving the database to a dedicated instance on a different server :
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply