Re: Processes seen in enterprise manager

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Jacco Schalkwijk (NOSPAMjaccos_at_eurostop.co.uk)
Date: 04/30/04 

Date: Fri, 30 Apr 2004 13:35:27 +0100

I have worked with one third party application whose idea of connection
pooling was to open up 100 connections on start-up, even though it never
used more than 2 during the time we used it. Your third party application
might have been designed by a similarly brilliant and knowledgeable
developer.

You can't set a timeout for the connections, but you can schedule a job to
run the following script on a regular basis. This example kills all
connections that have not been used for 6 hours:

DECLARE @sql varchar(4000)
WHILE 1=1
BEGIN
    SET @sql = (SELECT TOP 1 'KILL ' + CAST(spid AS VARCHAR(10))
        FROM master.dbo.sysprocesses WHERE DATEDIFF(hh, last_batch,
GETDATE()) >= 6
        AND spid <> @@spid AND spid >= 50)
        IF @sql IS NULL BREAK
       EXEC (@sql)
END 

-- 
Jacco Schalkwijk
SQL Server MVP
"Wayne Antinore" <wantinore@veramark.com> wrote in message
news:OPecmwqLEHA.1340@TK2MSFTNGP12.phx.gbl...
> Hi,
> We are using a new third party application that has SQL Server 2000 as the
> database.  It is an ASP page front end and uses ODBC to connect.  There
are
> about 20 people who use it during the day.  When I look in EM at the
> processes there are well over 100.  Even in the morning after everyone has
> logged out the night before.  All the processes are sleeping so they
aren't
> using any resources.  I feel kind of dumb here but is there a server
> property setting where I can set a value for these to expire?  Looked in
BOL
> and in my other books but this doesn't seem to be available.  Only timeout
> settings when waiting for a connection or running a query.  I wasn't
worried
> about these thinking SQL Server was managing them but then I noticed that
> our in house application that uses the same type of setup doesn't have
this
> problem.
>
>   Thanks,
>       Wayne
>
>

Relevant Pages

  • Re: Processes seen in enterprise manager
    ... I'll give the script a try. ... > You can't set a timeout for the connections, but you can schedule a job to ... >> property setting where I can set a value for these to expire? ... >> about these thinking SQL Server was managing them but then I noticed ...
    (microsoft.public.sqlserver.server)
  • Re: Nightmare Journeys (when Multiple Advance Purchase goes wrong)
    ... The fact that a third party ... screwed up the connections was never going to make GNER throw a party ... things go wrong - they can sort out who compensates who later. ...
    (uk.railway)
  • Re: How can I tell what programs are using what connections?
    ... "chad" wrote in message ... >I want to be able to tell what programs have opened what connections on ... as well as some third party ones I've seen can do it. ... Can I just read some data to find out, or do I need to hook ...
    (microsoft.public.win32.programmer.networks)
  • Re: SQL Server does not exist/ Access Denied
    ... This issue was caused in our case due to a number of open SQL ... Connections (Connection Pooling). ... Never Buy Third party tools if the application can be ...
    (microsoft.public.sqlserver.connect)
  • Re: SQL Server does not exist/ Access Denied
    ... Connections (Connection Pooling). ... Never Buy Third party tools if the application can be ...
    (microsoft.public.sqlserver.connect)