Post reply

sp_reset_connection

  • September 4, 2003 at 4:02 am

    #162347

    My understanding of sp_reset_connection is that SQL Server executes this SP every so often so that the settings for a single connection within a pool are reset. Thus allowing the same connection to be used indefinately.

    However I seem to be getting errors saying that there are no pooled connections available, so I have tried following the connection in SQL Profiler and it seems to use the same LoginSID.

    Is there any other way of monitoring the same connection to ensure that it is always remaining as one connection and not creating new connections?

    The above error happens when the DB server is not in use by anyone or anything (no scheduled jobs etc), at 1 am in the morning.

    Thanks in advance.

    Carl

  • September 8, 2003 at 12:00 pm

    This was removed by the editor as SPAM

  • September 8, 2003 at 7:37 pm

    #473136

    Hi there

    I believe the problem is related to your provider and and properties set on connection to the instance. Also, what version are you running and service pack? i have read posts on google realted to issues resolved in sp2 of sqlserver 2k.

    Have a read of this:

    http://groups.google.com.au/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=011301c2de3d%246f56b540%243001280a%40phx.gbl&rnum=1&prev=/groups%3Fq%3Dsp_reset_connection%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26selm%3D011301c2de3d%25246f56b540%25243001280a%2540phx.gbl%26rnum%3D1

    namely:

    After some research I found that this is known problem

    with the MS OLEDB and ODBC data providers,even the latest

    ones. It's cause is connection pooling,which apparently

    ADO uses by default.You can add this code to your

    connection string to disable connection pooling,which

    eliminates the error:

    OLE DB Services=-2;

    Probably not your problem but worth looking at.

    Cheers

    Ck

    Chris Kempster

    http://www.chriskempster.com

    Author of "SQL Server 2k for the Oracle DBA"


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply