SQL Server Performance

WHY ADO _ConnectionPtr->State = adStateOpen

Discussion in 'T-SQL Performance Tuning for Developers' started by mtmingus, Nov 5, 2003.

  1. mtmingus New Member

    even after database is shutdown, for example, thru the services.
    I didn't close the connection thru the program.

    If the db is down, dead, how can ADO _ConnectionPtr still connected
    to database (which is nowhere to find)?

    What is a more reliable/least expensive why I can use to find out
    if the db is alive (other than a select stmt to a user table)?

    Least expensive, I meant, I have to run this constantly, should cost
    very little when the db is alive and connected.
  2. satya Moderator

  3. mtmingus New Member

    We use ADO(OLEDB), not ODBC.
    Couldn't find any ping utility from the install folder.

    quote:Originally posted by satya

    As specified in this KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;138541 you can use ODBCPING utility to check the server status and its ideal to pass the select query to check the database status.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com

  4. satya Moderator

    SQL will not terminate the connection until client disconnects or SQL services are refreshed.
    Have you checked in the current activity for the status.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  5. Argyle New Member

    If connection pooling (or resource pooling for OLEDB) is used (it's on by default on most systems) the ADO _ConnectionPtr can point to a connection in the pool even if the underlying server is offline. You need to check the connection status of the object in the pool to determine if the server is online or offline.

    Here is a link on the subject (see "Connection Status"):
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html/pooling2.asp

    For the monitoring application you could turn off pooling. And instead of running a query against a table you could just connect to the master database and do a "SELECT 1+1" or something.

    /Argyle
  6. mtmingus New Member

    Thanks!

    quote:Originally posted by Argyle

    If connection pooling (or resource pooling for OLEDB) is used (it's on by default on most systems) the ADO _ConnectionPtr can point to a connection in the pool even if the underlying server is offline. You need to check the connection status of the object in the pool to determine if the server is online or offline.

    Here is a link on the subject (see "Connection Status"):
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html/pooling2.asp

    For the monitoring application you could turn off pooling. And instead of running a query against a table you could just connect to the master database and do a "SELECT 1+1" or something.

    /Argyle

Share This Page