Error: 18056, Severity: 20, State: 23

Question

Error: 18056, Severity: 20, State: 23

SQL Galaxy

One Orange Chip

Points: 29472
More actions
August 1, 2017 at 11:32 pm

#327834

Hi..
Error: 18056, Severity: 20, State: 23.
Message
The client was unable to reuse a session with SPID 148, which had been reset for connection pooling. The failure ID is 23. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
Could you please suggest how to fix it?
Server OS - Windows 2008 R2, Database: SQL 2008 R2 64 bit and SP3, 10.50.6220.0
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 1

SQL Galaxy - Tuesday, August 1, 2017 11:32 PM
Hi..
Error: 18056, Severity: 20, State: 23.
Message
The client was unable to reuse a session with SPID 148, which had been reset for connection pooling. The failure ID is 23. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
Could you please suggest how to fix it?
Server OS - Windows 2008 R2, Database: SQL 2008 R2 64 bit and SP3, 10.50.6220.0
Thanks

Have you checked the error logs for further information? It is probably due to resources being busy.

Thanks

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 2

i have checked error log file and found those error messages.. Please suggest which resource are busy in terms of CPU, memory. I have checked these resource are normal state CPU 40 % and memory 70%. total server memory 64 GB and MAXDOP allocated 52GB.
Also i chcked ring_buffer by using script http://thinknook.com/sql-server-connection-reset-for-connection-pooling-2012-02-26/ and found more than 1000 records found such as connection closed.

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 3

SQL Galaxy - Wednesday, August 2, 2017 1:28 AM
i have checked error log file and found those error messages.. Please suggest which resource are busy in terms of CPU, memory. I have checked these resource are normal state CPU 40 % and memory 70%. total server memory 64 GB and MAXDOP allocated 52GB.
Also i chcked ring_buffer by using script http://thinknook.com/sql-server-connection-reset-for-connection-pooling-2012-02-26/ and found more than 1000 records found such as connection closed.

Are there no other messages in the error log prior to the error to throw a bit of light on to what was happening at the time?

Thanks

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 4

[
Date,Source,Severity,Message
08/02/2017 04:20:38,spid8s,Unknown,SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
08/02/2017 04:20:30,spid12s,Unknown,Service Broker manager has shut down.
08/02/2017 04:20:29,spid12s,Unknown,The current event was not reported to the Windows Events log. Operating system error = (null). You may need to clear the Windows Events log if it is full.
08/02/2017 04:20:29,spid12s,Unknown,Error: 17054<c/> Severity: 16<c/> State: 1.
08/02/2017 04:20:29,spid12s,Unknown,The Database Mirroring protocol transport has stopped listening for connections.
08/02/2017 04:20:26,spid148,Unknown,The client was unable to reuse a session with SPID 148<c/> which had been reset for connection pooling. The failure ID is 23. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
08/02/2017 04:20:26,spid148,Unknown,Error: 18056<c/> Severity: 20<c/> State: 23.
08/02/2017 04:20:26,spid385,Unknown,The client was unable to reuse a session with SPID 385<c/> which had been reset for connection pooling. The failure ID is 23. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
08/02/2017 04:20:26,spid385,Unknown,Error: 18056<c/> Severity: 20<c/> State: 23.
08/02/2017 04:20:21,Server,Unknown,SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required.

/code]

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 5

SQL Galaxy - Wednesday, August 2, 2017 3:26 AM
[
Date,Source,Severity,Message
08/02/2017 04:20:38,spid8s,Unknown,SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
08/02/2017 04:20:30,spid12s,Unknown,Service Broker manager has shut down.
08/02/2017 04:20:29,spid12s,Unknown,The current event was not reported to the Windows Events log. Operating system error = (null). You may need to clear the Windows Events log if it is full.
08/02/2017 04:20:29,spid12s,Unknown,Error: 17054<c/> Severity: 16<c/> State: 1.
08/02/2017 04:20:29,spid12s,Unknown,The Database Mirroring protocol transport has stopped listening for connections.
08/02/2017 04:20:26,spid148,Unknown,The client was unable to reuse a session with SPID 148<c/> which had been reset for connection pooling. The failure ID is 23. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
08/02/2017 04:20:26,spid148,Unknown,Error: 18056<c/> Severity: 20<c/> State: 23.
08/02/2017 04:20:26,spid385,Unknown,The client was unable to reuse a session with SPID 385<c/> which had been reset for connection pooling. The failure ID is 23. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
08/02/2017 04:20:26,spid385,Unknown,Error: 18056<c/> Severity: 20<c/> State: 23.
08/02/2017 04:20:21,Server,Unknown,SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required.
/code]

The error was generated because your server shutdown. Do you know the reason for the shutdown?

Thanks

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 6

1. Received call from user side for application hang and not responding..
2. Login server and checked database instance both were up/running state.
3. Server take restart manually at 08/02/2017 04:20:21 AM after that application got working.

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 7

SQL Galaxy - Wednesday, August 2, 2017 4:01 AM
1. Received call from user side for application hang and not responding..
2. Login server and checked database instance both were up/running state.
3. Server take restart manually at 08/02/2017 04:20:21 AM after that application got working.

Was there any blocking on the database when it was reported that the application was not responding?
Have you checked the application logs for any errors?

Thanks

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 8

Hi NorthernSoul.
Nothing found in blocking and deadlocking at that time...
I checked event viewer application logs/system - no error found related database.
One error found at system logs.

The shadow copies of volume E: were aborted because the shadow copy storage could not grow due to a user imposed limit. logged time 8/2/2017 2:01:12 AM.
it might be index rebuild jobs running every day at that same time. total DB size 256GB and LDF file size 30 GB