|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Activity MonitorWhen 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 ;-) Show quote "Tlink" wrote: > 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 set pooling=false and rechecked the refresh rate on the activity monitor
and no change it still mounts. Show quote "migeold" <mige***@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: > >> 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. >> >> >> Can you post your connectionstring and some more information to reproduce
your problem, please? Show quote "Tlink" wrote: > > Yes set pooling=false and rechecked the refresh rate on the activity monitor > and no change it still mounts. > > > "migeold" <mige***@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: > > > >> 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. > >> > >> > >> > > > 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 : RTSOBJ.close:processed : Loop RTSOBJ2.close RTSOBJ3.close dbobj.close Show quote "migeold" <mige***@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: > >> >> Yes set pooling=false and rechecked the refresh rate on the activity >> monitor >> and no change it still mounts. >> >> >> "migeold" <mige***@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: >> > >> >> 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 refresh the monitor?
-- Show quoteAndrew 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.
Show quote "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... >> 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. >> > > 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> <uoFITgnTGHA.5***@tk2msftngp13.phx.gbl>>From: "Tlink" <Tlink@online.nospam> >References: <eiqciSmTGHA.4***@TK2MSFTNGP10.phx.gbl> Show quote >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.1***@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... >> 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. >>> >> >> > > > Not really sure what I am looking for, sorry can you please provide a little
bit more information. ""privatenews"" <pet***@online.microsoft.com> wrote in message Show quote 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. > > > -------------------- >>Reply-To: "Tlink" <Tlink@online.nospam> >>From: "Tlink" <Tlink@online.nospam> >>References: <eiqciSmTGHA.4***@TK2MSFTNGP10.phx.gbl> > <uoFITgnTGHA.5***@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.1***@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... >>> 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. >>>> >>> >>> >> >> >> > 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> <uoFITgnTGHA.5***@tk2msftngp13.phx.gbl> >From: "Tlink" <Tlink@online.nospam> >References: <eiqciSmTGHA.4***@TK2MSFTNGP10.phx.gbl> <#JWRSMoTGHA.1***@tk2msftngp13.phx.gbl> <Sw6WnjuTGHA.4***@TK2MSFTNGXA01.phx.gbl> Show quote >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.4***@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"" <pet***@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. >> >> >> -------------------- >>>Reply-To: "Tlink" <Tlink@online.nospam> >>>From: "Tlink" <Tlink@online.nospam> >>>References: <eiqciSmTGHA.4***@TK2MSFTNGP10.phx.gbl> >> <uoFITgnTGHA.5***@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.1***@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... >>>> 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. >>>>> >>>> >>>> >>> >>> >>> >> > > >  |
|||||||||||||||||||||||
Other interesting topics