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 . [SQLSTATE 08001]  ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]
SQLServer Error: 258, Unable to complete login process due to delay in prelogin response [SQLSTATE 08001]  Logon to server '(local)' failed (ConnUpdateStartExecutionDate)