Activity Monitor

When I execute a task against my mssql 2005 server it remains visible in the
activity monitor for up to 10 minutes (status = sleeping. open transactions
= 0, command = awaiting command), I have tried and changed the
commandtimeout = 10, reset the connectiontimeout and still it remains. It
appears that they are consuming resources, my concern is that we have up to
5 million requests a months and as a result the table is exceptionally
large.
Any suggestions would be greatly appreciated.Did you think about connection pooling?
Connection pooling will hold a connection for a specific amount of time for
reuse.
You can disable connection pooling in your connectionstring
...pooling=false...
Don't forget to refresh your activity monitor ;-)
"Tlink" wrote:

> When I execute a task against my mssql 2005 server it remains visible in t
he
> activity monitor for up to 10 minutes (status = sleeping. open transaction
s
> = 0, command = awaiting command), I have tried and changed the
> commandtimeout = 10, reset the connectiontimeout and still it remains. It
> appears that they are consuming resources, my concern is that we have up t
o
> 5 million requests a months and as a result the table is exceptionally
> large.
> Any suggestions would be greatly appreciated.
>
>|||Did you refresh the monitor?
Andrew J. Kelly SQL MVP
"Tlink" <Tlink@.online.nospam> wrote in message
news:eiqciSmTGHA.4308@.TK2MSFTNGP10.phx.gbl...
> When I execute a task against my mssql 2005 server it remains visible in
> the activity monitor for up to 10 minutes (status = sleeping. open
> transactions = 0, command = awaiting command), I have tried and changed
> the commandtimeout = 10, reset the connectiontimeout and still it remains.
> It appears that they are consuming resources, my concern is that we have
> up to 5 million requests a months and as a result the table is
> exceptionally large.
> Any suggestions would be greatly appreciated.
>|||Yes, I set the monitor to refresh ever 10 seconds.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uoFITgnTGHA.5900@.tk2msftngp13.phx.gbl...
> Did you refresh the monitor?
> --
> Andrew J. Kelly SQL MVP
>
> "Tlink" <Tlink@.online.nospam> wrote in message
> news:eiqciSmTGHA.4308@.TK2MSFTNGP10.phx.gbl...
>|||Yes set pooling=false and rechecked the refresh rate on the activity monitor
and no change it still mounts.
"migeold" <migeold@.discussions.microsoft.com> wrote in message
news:9697B6DF-77A8-43B0-B6C7-1884420764BD@.microsoft.com...
> Did you think about connection pooling?
> Connection pooling will hold a connection for a specific amount of time
> for
> reuse.
> You can disable connection pooling in your connectionstring
> ...pooling=false...
> Don't forget to refresh your activity monitor ;-)
> "Tlink" wrote:
>|||Can you post your connectionstring and some more information to reproduce
your problem, please?
"Tlink" wrote:

> Yes set pooling=false and rechecked the refresh rate on the activity monit
or
> and no change it still mounts.
>
> "migeold" <migeold@.discussions.microsoft.com> wrote in message
> news:9697B6DF-77A8-43B0-B6C7-1884420764BD@.microsoft.com...
>
>|||Here is an extract of the connection string and some data collection. The
average number of records per database table is 5.6 million records, I have
optimised the files and they all contain indexes.
2 connection string
ConnectionString = "Provider=SQLOLEDB.1;"
or
ConnectionString = "Provider=SQLNCLI;"
ConnectionString = ConnectionString & ";MarsConn=yes"
One of the above sets &
ConnectionString = ConnectionString & ";Database=" & dbname
ConnectionString = ConnectionString & ";Uid=" & dbuid
ConnectionString = ConnectionString & ";Pwd=" & dbpassword
ConnectionString = ConnectionString & ";Encrypt=yes"
ConnectionString = ConnectionString & ";Pooling=false"
DBobj.connectionstring = ConnectionString
DBobj.CommandTimeout = 30
dbobj.open
dbobj.commandtimeout = 10
RTSOBJ = dbobj.execute("Select xxx,bbb,ccc,ddd,eee FROM ControlFile where
ooo = 'pppp';")
RTSOBJ2 = dbobj.execute("Select ffff,hhhh,iii,kkkk,llll FROM ControlFile2
where hhhh = 'pppp';")
RTSOBJ3 = dbobj.execute("Select * FROM ControlFile2 where hhhh = 'pppp';")
' all data returned has at least 1 record but may contain up to 100 records
which are processed in a loop
do while RTSOBJ.EOF = FALSE
:
:processed
:
Loop
RTSOBJ.close
RTSOBJ2.close
RTSOBJ3.close
dbobj.close
"migeold" <migeold@.discussions.microsoft.com> wrote in message
news:D2A10843-7293-4E2D-B3AD-5BBB62242B62@.microsoft.com...
> Can you post your connectionstring and some more information to reproduce
> your problem, please?
> "Tlink" wrote:
>|||Hello,
You may want to check sys.sysprocesses view to see if there is any
difference before and after you close the connection.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Reply-To: "Tlink" <Tlink@.online.nospam>
>From: "Tlink" <Tlink@.online.nospam>
>References: <eiqciSmTGHA.4308@.TK2MSFTNGP10.phx.gbl>
<uoFITgnTGHA.5900@.tk2msftngp13.phx.gbl>
>Subject: Re: Activity Monitor
>Date: Fri, 24 Mar 2006 01:07:03 +1100
>Lines: 26
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
>X-RFC2646: Format=Flowed; Response
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
>Message-ID: <#JWRSMoTGHA.1672@.tk2msftngp13.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.programming
>NNTP-Posting-Host: bus-210-211-121-210.vic.veridas.net 210.211.121.210
>Path: TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
>Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.programming:588412
>X-Tomcat-NG: microsoft.public.sqlserver.programming
>Yes, I set the monitor to refresh ever 10 seconds.
>"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>news:uoFITgnTGHA.5900@.tk2msftngp13.phx.gbl...
in
that
>
>|||Not really sure what I am looking for, sorry can you please provide a little
bit more information.
""privatenews"" <petery@.online.microsoft.com> wrote in message
news:Sw6WnjuTGHA.4768@.TK2MSFTNGXA01.phx.gbl...
> Hello,
> You may want to check sys.sysprocesses view to see if there is any
> difference before and after you close the connection.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> --
> <uoFITgnTGHA.5900@.tk2msftngp13.phx.gbl>
> in
> that
>|||Hello,
To isolate if the issue is issue in activty monitor, you could monitor the
active process via the following query
select * from master.sys.sysprocesses
Usually if a connection is closed by client program, a process with SPID is
also terminated on server.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Reply-To: "Tlink" <Tlink@.online.nospam>
>From: "Tlink" <Tlink@.online.nospam>
>References: <eiqciSmTGHA.4308@.TK2MSFTNGP10.phx.gbl>
<uoFITgnTGHA.5900@.tk2msftngp13.phx.gbl>
<#JWRSMoTGHA.1672@.tk2msftngp13.phx.gbl>
<Sw6WnjuTGHA.4768@.TK2MSFTNGXA01.phx.gbl>
>Subject: Re: Activity Monitor
>Date: Sat, 25 Mar 2006 18:57:34 +1100
>Lines: 80
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
>X-RFC2646: Format=Flowed; Original
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
>Message-ID: <#mitkH#TGHA.4300@.TK2MSFTNGP14.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.programming
>NNTP-Posting-Host: bus-210-211-121-210.vic.veridas.net 210.211.121.210
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.programming:588696
>X-Tomcat-NG: microsoft.public.sqlserver.programming
>
>Not really sure what I am looking for, sorry can you please provide a
little
>bit more information.
>
>""privatenews"" <petery@.online.microsoft.com> wrote in message
>news:Sw6WnjuTGHA.4768@.TK2MSFTNGXA01.phx.gbl...
changed
>
>