I applied a service pack to a third party application and
as of now I keep receiving the following in the sql
server log and application log every 1 minute or so.
How can I use the profiler on this to find out what's
happening?
Event Type: Information
Event Source: MSSQLSERVER
Event Category: (4)
Event ID: 17055
Date: 12/28/2003
Time: 11:54:26 AM
User: N/A
Computer: SERVERP01
Description:
18456 :
Login failed for user 'TRACK'.
Data:
0000: 18 48 00 00 0e 00 00 00 .H.....
0008: 0f 00 00 00 4a 00 52 00 ...J.R.
0010: 41 00 59 00 44 00 55 00 A.Y.D.U.
0018: 42 00 49 00 53 00 51 00 B.I.S.Q.
0020: 4c 00 50 00 30 00 31 00 L.P.0.1.
0028: 00 00 07 00 00 00 6d 00 .....m.
0030: 61 00 73 00 74 00 65 00 a.s.t.e.
0038: 72 00 00 00 r...
Thanks,
ShonetYou can use profiler to log connection attempts...Someone is trying to log
in using the login TRACK, which is a failed attempt... Watching this in
profiler will get you the name of the workstation, and name of the program
they are using... then you can track them down..
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Shonet" <anonymous@.discussions.microsoft.com> wrote in message
news:02bc01c3cd17$897e17f0$a601280a@.phx.gbl...
> I applied a service pack to a third party application and
> as of now I keep receiving the following in the sql
> server log and application log every 1 minute or so.
> How can I use the profiler on this to find out what's
> happening?
> Event Type: Information
> Event Source: MSSQLSERVER
> Event Category: (4)
> Event ID: 17055
> Date: 12/28/2003
> Time: 11:54:26 AM
> User: N/A
> Computer: SERVERP01
> Description:
> 18456 :
> Login failed for user 'TRACK'.
> Data:
> 0000: 18 48 00 00 0e 00 00 00 .H.....
> 0008: 0f 00 00 00 4a 00 52 00 ...J.R.
> 0010: 41 00 59 00 44 00 55 00 A.Y.D.U.
> 0018: 42 00 49 00 53 00 51 00 B.I.S.Q.
> 0020: 4c 00 50 00 30 00 31 00 L.P.0.1.
> 0028: 00 00 07 00 00 00 6d 00 .....m.
> 0030: 61 00 73 00 74 00 65 00 a.s.t.e.
> 0038: 72 00 00 00 r...
>
> Thanks,
> Shonet|||I managed to work this one out, but I'm please to see
what I came up with is was the same as your solution.
Excellent.
Thanks!!
Shonet
>--Original Message--
>You can use profiler to log connection
attempts...Someone is trying to log
>in using the login TRACK, which is a failed attempt...
Watching this in
>profiler will get you the name of the workstation, and
name of the program
>they are using... then you can track them down..
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>
>"Shonet" <anonymous@.discussions.microsoft.com> wrote in
message
>news:02bc01c3cd17$897e17f0$a601280a@.phx.gbl...
>> I applied a service pack to a third party application
and
>> as of now I keep receiving the following in the sql
>> server log and application log every 1 minute or so.
>> How can I use the profiler on this to find out what's
>> happening?
>> Event Type: Information
>> Event Source: MSSQLSERVER
>> Event Category: (4)
>> Event ID: 17055
>> Date: 12/28/2003
>> Time: 11:54:26 AM
>> User: N/A
>> Computer: SERVERP01
>> Description:
>> 18456 :
>> Login failed for user 'TRACK'.
>> Data:
>> 0000: 18 48 00 00 0e 00 00 00 .H.....
>> 0008: 0f 00 00 00 4a 00 52 00 ...J.R.
>> 0010: 41 00 59 00 44 00 55 00 A.Y.D.U.
>> 0018: 42 00 49 00 53 00 51 00 B.I.S.Q.
>> 0020: 4c 00 50 00 30 00 31 00 L.P.0.1.
>> 0028: 00 00 07 00 00 00 6d 00 .....m.
>> 0030: 61 00 73 00 74 00 65 00 a.s.t.e.
>> 0038: 72 00 00 00 r...
>>
>> Thanks,
>> Shonet
>
>.
>
Showing posts with label party. Show all posts
Showing posts with label party. Show all posts
Wednesday, March 28, 2012
Monday, March 12, 2012
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,
Wayne
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
>
|||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...[vbcol=seagreen]
> 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...
the[vbcol=seagreen]
> are
has[vbcol=seagreen]
> aren't
> BOL
timeout[vbcol=seagreen]
> worried
that
> this
>
|||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[vbcol=seagreen]
> 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...
application[vbcol=seagreen]
to[vbcol=seagreen]
> the
There[vbcol=seagreen]
> has
in
> timeout
> that
>
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
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
>
|||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...[vbcol=seagreen]
> 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...
the[vbcol=seagreen]
> are
has[vbcol=seagreen]
> aren't
> BOL
timeout[vbcol=seagreen]
> worried
that
> this
>
|||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[vbcol=seagreen]
> 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...
application[vbcol=seagreen]
to[vbcol=seagreen]
> the
There[vbcol=seagreen]
> has
in
> timeout
> that
>
Friday, March 9, 2012
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...
the[vbcol=seagreen]
> are
has[vbcol=seagreen]
> aren't
> BOL
timeout[vbcol=seagreen]
> worried
that[vbcol=seagreen]
> this
>|||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...
application[vbcol=seagreen]
to[vbcol=seagreen]
> the
There[vbcol=seagreen]
> has
in[vbcol=seagreen]
> timeout
> that
>
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...
the[vbcol=seagreen]
> are
has[vbcol=seagreen]
> aren't
> BOL
timeout[vbcol=seagreen]
> worried
that[vbcol=seagreen]
> this
>|||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...
application[vbcol=seagreen]
to[vbcol=seagreen]
> the
There[vbcol=seagreen]
> has
in[vbcol=seagreen]
> timeout
> that
>
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
> > >
> > >
> >
> >
>
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
> > >
> > >
> >
> >
>
Subscribe to:
Posts (Atom)