Processes seen in enterprise manager

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,
WayneI 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
>|||Thanks Jacco,
I'll give the script a try. Based on other things I've seen with this app I
would not be surprised if they are doing something similar.
Thanks again,
Wayne
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23m6mO%23qLEHA.3516@.TK2MSFTNGP11.phx.gbl...
> 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
> >
> >
>|||Ok, don't be surprised though if the application either fails to work when
it's 100 or so connections aren't there, or just recreates them on a regular
basis after you have killed them....
--
Jacco Schalkwijk
SQL Server MVP
"Wayne Antinore" <wantinore@.veramark.com> wrote in message
news:uJQkvJrLEHA.3904@.TK2MSFTNGP09.phx.gbl...
> Thanks Jacco,
> I'll give the script a try. Based on other things I've seen with this app
I
> would not be surprised if they are doing something similar.
> Thanks again,
> Wayne
>
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:%23m6mO%23qLEHA.3516@.TK2MSFTNGP11.phx.gbl...
> > 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
> > >
> > >
> >
> >
>