RE: SQL Server 2000 Connection Timeout Issues

Hi in SQL Profiling you can use the following Events to determine if there is
a issue this will not be much more info but might help:

Security Audit\Audit Login Failed
Errors and Warnings\Attention
Errors and Warnings\OLEDB Errors

Another thing to consider, I haven't coded anything using SQL native client
to connect to SQL 2000; try using the SQL OLE DB connection instead maybe?

Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005


"Greg J." wrote:

What is the cause of SQL Server connection timeout errors? One application (a
very complex SQL Server 2005 SSIS package) reports the following error:

An OLE DB record is available. Source: "Microsoft SQL Native Client"
Hresult: 0x80004005 Description: "Login timeout expired".
An OLE DB record is available. Source: "Microsoft SQL Native Client"
Hresult: 0x80004005 Description: "Unable to complete login process due to
delay in prelogin response".
An OLE DB record is available. Source: "Microsoft SQL Native Client"
Hresult: 0x80004005 Description: "TCP Provider: Timeout error [258]. ".

We are running SQL Server 2000 SP4 (build 2162) Enterprise Edition.

Other applications are reporting similar errors at the same time (but we do
not have the exact error messages).

The server is hosting several databases, with about 300 simultaneous
connections per day. However, the SQL Server Error Log does not report any
errors during these periods. Two applications (poorly written) constantly log
in and log out of the server several times a second over a 2-5 minute period
several times a day. One is a SQL Server 2005 SSIS package and the other is a
Microsoft Access 2003 application accessing the server through linked tables.

The following error log fragment included below is a sample 2-second
interval. The SSIS package logs in as login appReader and the Access
application logs in as a trusted connection (exact names changed).

2007-10-03 15:18:51.03 logon Login succeeded for user 'appReader'.
Connection: Non-Trusted.
2007-10-03 15:18:51.03 logon Login succeeded for user 'appReader'.
Connection: Non-Trusted.
2007-10-03 15:18:51.08 logon Login succeeded for user 'appReader'.
Connection: Non-Trusted.
2007-10-03 15:18:51.08 logon Login succeeded for user 'appReader'.
Connection: Non-Trusted.
2007-10-03 15:18:51.81 logon Login succeeded for user 'appReader'.
Connection: Non-Trusted.
2007-10-03 15:18:51.83 logon Login succeeded for user 'appReader'.
Connection: Non-Trusted.
2007-10-03 15:18:51.86 logon Login succeeded for user 'appReader'.
Connection: Non-Trusted.
2007-10-03 15:18:51.89 logon Login succeeded for user 'appReader'.
Connection: Non-Trusted.
2007-10-03 15:18:52.03 logon Login succeeded for user 'appReader'.
Connection: Non-Trusted.
2007-10-03 15:18:52.05 logon Login succeeded for user 'appReader'.
Connection: Non-Trusted.
2007-10-03 15:18:52.08 logon Login succeeded for user 'appReader'.
Connection: Non-Trusted.
2007-10-03 15:18:52.09 logon Login succeeded for user 'appReader'.
Connection: Non-Trusted.
2007-10-03 15:18:52.11 logon Login succeeded for user 'appReader'.
Connection: Non-Trusted.
2007-10-03 15:18:52.13 logon Login succeeded for user 'MYDOMAIN\JDoe'.
Connection: Trusted.
2007-10-03 15:18:52.14 logon Login succeeded for user 'appReader'.
Connection: Non-Trusted.
2007-10-03 15:18:52.14 logon Login succeeded for user 'MYDOMAIN\JDoe'.
Connection: Trusted.
2007-10-03 15:18:52.16 logon Login succeeded for user 'MYDOMAIN\JDoe'.
Connection: Trusted.
2007-10-03 15:18:52.16 logon Login succeeded for user 'appReader'.
Connection: Non-Trusted.
2007-10-03 15:18:52.16 logon Login succeeded for user 'MYDOMAIN\JDoe'.
Connection: Trusted.
2007-10-03 15:18:52.17 logon Login succeeded for user 'MYDOMAIN\JDoe'.
Connection: Trusted.
2007-10-03 15:18:52.17 logon Login succeeded for user 'appReader'.
Connection: Non-Trusted.
2007-10-03 15:18:52.19 logon Login succeeded for user 'MYDOMAIN\JDoe'.
Connection: Trusted.
2007-10-03 15:18:52.20 logon Login succeeded for user 'MYDOMAIN\JDoe'.
Connection: Trusted.
2007-10-03 15:18:52.22 logon Login succeeded for user 'MYDOMAIN\JDoe'.
Connection: Trusted.
2007-10-03 15:18:52.24 logon Login succeeded for user 'MYDOMAIN\JDoe'.
Connection: Trusted.
2007-10-03 15:18:52.27 logon Login succeeded for user 'appReader'.
Connection: Non-Trusted.
2007-10-03 15:18:52.28 logon Login succeeded for user 'appReader'.
Connection: Non-Trusted.
2007-10-03 15:18:52.30 logon Login succeeded for user 'appReader'.
Connection: Non-Trusted.
2007-10-03 15:18:52.33 logon Login succeeded for user 'appReader'.
Connection: Non-Trusted.
2007-10-03 15:18:52.34 logon Login succeeded for user 'appReader'.
Connection: Non-Trusted.
2007-10-03 15:18:52.38 logon Login succeeded for user 'appReader'.
Connection: Non-Trusted.
2007-10-03 15:18:52.41 logon Login succeeded for user 'appReader'.
Connection: Non-Trusted.
2007-10-03 15:18:52.44 logon Login succeeded for user 'appReader'.
Connection: Non-Trusted.
2007-10-03 15:18:52.45 logon Login succeeded for user 'appReader'.
Connection: Non-Trusted.
2007-10-03 15:18:52.55 logon Login succeeded for user 'appReader'.
Connection: Non-Trusted.
2007-10-03 15:18:52.59 logon Login succeeded for user 'appReader'.
Connection: Non-Trusted.
2007-10-03 15:18:52.63 logon Login succeeded for user 'appReader'.
Connection: Non-Trusted.
2007-10-03 15:18:52.63 logon Login succeeded for user 'appReader'.
Connection: Non-Trusted.

Are there any more diagnostics that I can run to follow-up with this error?
I would like to prove or disprove that this is a SQL Server resource issue
and to determine if we need to move one or more of these applications to a
different server.

Would profiling help? Would System Monitor? And if so, what objects and
counters to monitor?

Are there any trace flags that I can run to show extra diagnostics?

Are these two applications be causing other applications failing to to
connect (or timing out) to the server?

.

Relevant Pages

  • Re: SQL Resets
    ... If it were SQL that was falling short, ... The default backlog for SQL Server is 5. ... System.InvalidOperationException: Internal connection fatal error. ... From time to time, under heavy loads, we are getting resets at ...
    (microsoft.public.sqlserver.connect)
  • Re: provider: Named Pipes Provider, error: 40 - Could not open a c
    ... I have tried connecting to Sql Management Studio and it worked pretty much ... An error has occurred while establishing a connection to the server. ... under the default settings SQL Server does not allow remote connections. ... I have a sql server install / database on the web server. ...
    (microsoft.public.sqlserver.connect)
  • .Net framework error
    ... Unfortunately the information is very limited and usually the report just ... The application is a front end for a SQL Server 2005 Express database, ... connection string in the app config file. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: connect to named instance w/ non-default port
    ... SQL Native Client Configuration. ... An error has occurred while establishing a connection to the server. ... under the default settings SQL Server does not allow remote connections. ...
    (microsoft.public.sqlserver.connect)
  • Re: Concurrent database access in SQL 2005 Mobile
    ... Please stick to having an "dummy" connection in Open State. ... SQL CE/Mobile has a background thread that flushes the changes ... they run for long hours and we have not seen memory leaks so far. ... then opens his work forms and it's in ...
    (microsoft.public.sqlserver.ce)
Loading