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?