Why are there so many processes running?

Greetings,
When I look in Enterprise Manager, under Management/Current Activity/Process Info, there are many processes running, and there shouldn't be. All of them say "SET TRANSACTION ISOLATION LEVEL READ COMMITTED" as their last tsql command batch. We're sporadically getting the message of "The maximum simultaneous user count of 25 licenses for this 'Enterprise Edition' server has been exceeded", and we shouldn't have this many processes running. Why aren't these processes ending?
Thanks,
RP

One simple application with some run-away code spawn more connections and processes than physically supported by any server. Please find out the application(s), logon(s) and machine(s) that are generating this high traffic. You should then identify the code used to connect to the databases(s). As an old pirate once said "Here lies the dragons".



Nathan H.O.
Moderator
SQL-Server-Performance.com

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by vbkenya</i><br />As an old pirate once said "Here lies the dragons".<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">Haha. Great line. I'm going to use that next time I troubleshoot some developer's code [<img src='/community/emoticons/emotion-2.gif' alt='' />]

Here's a couple queries that might help you out a tad. Just summarizes the connections a million ways to Wednesday.

PRINT 'connections by database'
PRINT ' '
SELECT
sd.name,
COUNT(sp.spid) AS HostConnections,
(SELECT COUNT(spid) FROM master.dbo.sysprocesses) AS TotalConnections
FROM
master.dbo.sysprocesses sp with(nolock)
INNER JOIN master.dbo.sysdatabases sd with(nolock) ON sp.dbid = sd.dbid
GROUP BY
sd.name
ORDER BY
2 DESC

PRINT 'connections by loginame'
PRINT ' '
SELECT
loginame,
--hostname,
COUNT(*) AS HostConnection
FROM
master.dbo.sysprocesses with(nolock)
GROUP BY
loginame--,
--hostname
ORDER BY
2 DESC

PRINT 'connections by host/program'
PRINT ' '
SELECT
program_name,
hostname,
COUNT(*) AS HostConnections,
(SELECT COUNT(*) FROM master.dbo.sysprocesses) AS TotalConnections
FROM
master.dbo.sysprocesses with(nolock)
GROUP BY
program_name,
hostname
ORDER BY
1,3

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.