up vote 0 down vote favorite
share [g+] share [fb]

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)

link|improve this question
feedback

1 Answer

up vote 0 down vote accepted

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?

link|improve this answer
The server is SQL dedicated The server memory settings were as default (min 0 max 2000GB) when the error occurred - we have since limited the max memory to 5GB I'd seen the link - and couldn't get much sense out of it other than 'if I remove a user or database - I'll see these error messages'. The database and the user were fine during and after the issues - is there some other wisdom there I'm missing? – Lukek Aug 24 '10 at 15:14
Investigating the connection pooling seems to have shown that our services are in fact pooling - previous experience with SQL 2000 had led me to believe that seeing an Audit Login/Logout meant that pooling wasn't working, but it appears that in SQL 2008, there is a neat sub class to show if the connection is pooled or not. Very Nice! stackoverflow.com/questions/279401/… – Lukek Aug 24 '10 at 15:15
well - it didn't crash last night - which was kind of expected as after a reboot it seems to be fine for a day or two. I found an article that recommend setting the max memory to leave some overhead as SQL 2005 doesn't alway handle memory pressure in time...blogs.msdn.com/b/slavao/archive/2006/11/13/…. But not sure if that applies in our case as we aren't seeing OOM error messages – Lukek Aug 25 '10 at 10:29
Setting max memory to leave one or two gb for the OS might prevent future reboots. I suspect you will know if you several days past when you would have rebooted. – jl. Aug 25 '10 at 15:15
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.