Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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)

share|improve this question

1 Answer 1

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?

share|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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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