SQL 2008 not accepting connections

networkingsql-server-2008

Setup:
SQL 2008 Standard SP1 64Bit on Windows 2008 – one CPU, 8GB of memory
The server is dedicated to SQL Server.
The server memory settings were as default (min 0 max 2000GB)

Connections from 8 websites and services – the services frequently checking for new tasks
8 databases approx 1GB to 5GB each

Recently upgraded from SQL 2000 on Windows 2003

Issue:
Recently got all the databases transferred over, and at times of heavy load overnight – especially when running CHECKDB – the server will stop allowing new connections with error messages detailed below.

SQL Backups continued to work during this time.

Rebooting the server gets us back up again.

I can work on moving jobs around and performance tuning to try and avoid overload/usage peaks, but the server will only get busier over time.

The services are using resource/connection pooling, and the load overnight is mostly index rebuilds, archiving & checkdb

Question:
So, is there a way to handle the 'overload/wont accept new connections' gracefully or prevent it from happening?

Does anyone have a handle on what might be happening here?

If the server gets overloaded and wont accept new connections, is the only solution to reboot?

Error Messages:
on the Website:
Unspecified error COM Error Number: -2147467259 (0x80004005)
[DBNETLIB][ConnectionOpen (PreLoginHandshake()).]General network error. Check your network documentation.

COM Error Number: -2147467259 (0x80004005) Cannot open database requested in login

In SQL Server
Error: 17189, Severity: 16, State: 1. SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection.

Error: 18456, Severity: 14, State: 46. Login failed for user Reason: Failed to open the database configured in the login object while revalidating the login on the connection.

Error: 18056, Severity: 20, State: 46. The client was unable to reuse a session with SPID 109, which had been reset for connection pooling. The failure ID is 46.

Error: 18456, Severity: 14, State: 5. Login failed for user Reason: Could not find a login matching the name provided.

BCP Error: SQLState = 08001, NativeError = 258 – Error = [Microsoft][SQL Server Native Client 10.0]Shared Memory Provider: Timeout error

Unable to complete login process due to delay in login response – SQLState = S1T00, NativeError = 0 – Error = [Microsoft][SQL Server Native Client 10.0]Login timeout expired

SQLServer Error: 258, Shared Memory Provider: Timeout error [258]. [SQLSTATE 08001]
[165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]

SQLServer Error: 258, Unable to complete login process due to delay in prelogin response [SQLSTATE 08001]
[382] Logon to server '(local)' failed (ConnUpdateStartExecutionDate)

Best Answer

You likely have seen this MS link: http://blogs.msdn.com/b/psssql/archive/2010/08/03/how-it-works-error-18056-the-client-was-unable-to-reuse-a-session-with-spid-which-had-been-reset-for-connection-pooling.aspx

Is the database server dedictated to SQL Server or running something else as well?

What are your memory settings for SQL Server?