Failure ID is 29

Question

Post reply

Failure ID is 29

namanthakral

Right there with Babe

Points: 736
More actions
June 21, 2012 at 12:21 am

#262611

Hello i am using MSSQL server 2008 i am not to much sound in db administration from few days i am getting this error
"The client was unable to reuse a session with SPID 103, which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message."
I am really not aware what this error is. When we start the server RAM (Memory) usage is like 53% and CPU is like 0-5 % it peeks up automatically and comes to about RAM 95-98% and CPU still is around 0-5% system in about a day or so because of this the system shuts down unexpectedly
2 years ago we had upgraded our system from sql 2k to sql 2008 and the system was working fine before but since few days i am receiving these errors
my system configuration is like
Xeon R e5440@2.83 ghz(2 procesors)
RAM 4GB
system type 64 bit os
OS in use windows web server 2008 version 6.0 SP2
Kindly help !!!!
Just an update I haven't mentioned the sql server version on the question it is
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Workgroup Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)

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

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

Pradyothana Shastry SSCertifiable Points: 5211 More actions · Answer 1

Check this http://support.microsoft.com/kb/2543687

- Pradyothana http://www.msqlserver.com

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 2

I'd be more worried about your server shutting down unexpectedly than this message, which is (I've found, anyway) largely nothing to be worried about. It occurs when a calling session loses the connection SPID due to a connection pool reset. There's a comprehensive article here 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 about the different error codes, it appears 29 is fairly generic (RedoLoginException). The author suggests using sys.dm_os_ring_buffers to 'track down the source'.

Another related URL gives this short description of the different types of ring_buffer_type errors in the DMV.

• RING_BUFFER_RESOURCE_MONITOR – memory state changes due to various types of memory pressure

• RING_BUFFER_MEMORY_BROKER – notification to components by memory broker advising them to grow, shrink or stay stable

• RING_BUFFER_SINGLE_PAGE_ALLOCATOR – when the Buffer Pool single page allocator turns on/off internal memory pressure

• RING_BUFFER_OOM – out-of-memory conditions

• RING_BUFFER_BUFFER_POOL – severe buffer pool failures, including buffer pool out-of-memory conditions

• RING_BUFFER_SCHEDULER – scheduler operations

• RING_BUFFER_SCHEDULER_MONITOR – scheduler health

• RING_BUFFER_EXCEPTION – list of exceptions

• RING_BUFFER_CLRAPPDOMAIN – state of AppDomains loaded in the system

• RING_BUFFER_SECURITY_ERROR – (new in SQL Server 2005 SP2) Windows API failure information

If I were you I would search the view for these events, mapping the timestamp to the date/time these errors occur. You'll need to transform the timestamp column from UNIX (epoch) time (seconds since 01/01/1970) to DATETIME. Here is a snippet for example that will return the SQL-friendly timestamp for the newest entry in sys.dm_os_ring_buffers:

DECLARE @tstamp BIGINT

DECLARE @newTime DATETIME

DECLARE @startTime DATETIME

SET @tstamp = ( SELECT TOP 1 timestamp FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = 'TYPE_GOES_HERE' ORDER BY timestamp DESC )

SET @startTime = '1970-01-01 00:00:00.000'

SET @newTime = DATEADD(ss,@tstamp,@startTime)

SELECT @newTime

Once you can pin down what sort of problem it is you can work on resolving it.

If I were in your shoes I would:

1) Cap memory usage of SQL Server to approximately 75% of capacity, except if the server is performing other duties i.e. acting as a webserver / app server.

EXEC sp_configure 'show advanced options', 1

RECONFIGURE WITH OVERRIDE

GO

EXEC sp_configure 'max server memory', -- NUMBER_GOES_HERE_IN_MB

RECONFIGURE WITH OVERRIDE

GO

EXEC sp_configure 'min server memory', -- NUMBER_GOES_HERE_IN_MB

RECONFIGURE WITH OVERRIDE

GO

An article from MS about server memory management is available here : http://msdn.microsoft.com/en-us/library/ms178067(v=sql.105).aspx

2) If you can swing the downtime, check the physical integrity of the memory by downloading memtestX86+ (http://www.memtest.org/ and running it from a CD on server restart. This will help identify any hardware issues.

3) Check the system error logs for unexpected hardware/memory related events.

4) Do some perfmon work, capturing in particular SQL Server:Buffer Manager counters, which will help diagnose whether you are facing memory constraints.

Hope this helps.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

namanthakral Right there with Babe Points: 736 More actions · Answer 3

Thanks Pradyothana for the quick response so i would update as per the kb but just 1 more question do you think upgrading ram would do the needful as the ram on our servers is like 4GB and Physical memory usage quite often is more than 95 % on the production system

Pradyothana Shastry SSCertifiable Points: 5211 More actions · Answer 4

Check the memory usage in perfmon, check target and available memory just adjust the memory once again and observe again.

We can set min and max as below.

EXEC sp_configure 'show advanced options', 1

RECONFIGURE WITH OVERRIDE

GO

EXEC sp_configure 'max server memory',

RECONFIGURE WITH OVERRIDE

GO

EXEC sp_configure 'min server memory',

RECONFIGURE WITH OVERRIDE

GO

- Pradyothana http://www.msqlserver.com

namanthakral Right there with Babe Points: 736 More actions · Answer 5

Thanks Derek for your kind help I am not that sound in administration but i would try and do as you had suggested. Thanks a ton for the update.

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 6

You're welcome. FYI upgrading the RAM could be pointless if you're on a 32-bit architecture.

Reason being, 2^32 = 4GB.

Best thing to do is manage the memory you have and if your requirements dictate a better infrastructure, upgrade to a server which will support a 64-bit architecture.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

namanthakral Right there with Babe Points: 736 More actions · Answer 7

Thanks Derek but i am not getting this i am on 2008 version 6.0 SP2 64 bit os

i think it is 64 bit am a bit confused now

namanthakral Right there with Babe Points: 736 More actions · Answer 8

I did a

SELECT * FROM sys.dm_os_ring_buffers AS dorb;

i am getting 3660 rows and this is 1 row

0x0000000000406240RING_BUFFER_EXCEPTION6815052<Record id = "7426" type ="RING_BUFFER_EXCEPTION" time ="6815052"><Exception><Task address="0x00000000047DA988"></Task><Error>200</Error><Severity>25</Severity><State>16</State><UserDefined>0</UserDefined></Exception><Stack><frame id = "0">0X00000000007C3E63</frame><frame id = "1">0X00000000008950C5</frame><frame id = "2">0X0000000000C1DC0D</frame><frame id = "3">0X0000000074FFBF40</frame><frame id = "4">0X0000000074F9964E</frame><frame id = "5">0X00000000775968B1</frame><frame id = "6">0X0000000000581D5C</frame><frame id = "7">0X00000000005827A3</frame><frame id = "8">0X0000000000612024</frame><frame id = "9">0X000000000049A3F2</frame><frame id = "10">0X000000000202E08D</frame><frame id = "11">0X00000000021A5156</frame><frame id = "12">0X00000000021A5EC1</frame><frame id = "13">0X000000000203A23E</frame><frame id = "14">0X0000000002A16361</frame><frame id = "15">0X000000000049D10B</frame></Stack></Record>

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 9

I did a
SELECT * FROM sys.dm_os_ring_buffers AS dorb;
i am getting 3660 rows and this is 1 row
0x0000000000406240 RING_BUFFER_EXCEPTION 6815052 <Record id = "7426" type ="RING_BUFFER_EXCEPTION" time ="6815052"><Exception><Task address="0x00000000047DA988"></Task><Error>200</Error><Severity>25</Severity><State>16</State><UserDefined>0</UserDefined></Exception><Stack><frame id = "0">0X00000000007C3E63</frame><frame id = "1">0X00000000008950C5</frame><frame id = "2">0X0000000000C1DC0D</frame><frame id = "3">0X0000000074FFBF40</frame><frame id = "4">0X0000000074F9964E</frame><frame id = "5">0X00000000775968B1</frame><frame id = "6">0X0000000000581D5C</frame><frame id = "7">0X00000000005827A3</frame><frame id = "8">0X0000000000612024</frame><frame id = "9">0X000000000049A3F2</frame><frame id = "10">0X000000000202E08D</frame><frame id = "11">0X00000000021A5156</frame><frame id = "12">0X00000000021A5EC1</frame><frame id = "13">0X000000000203A23E</frame><frame id = "14">0X0000000002A16361</frame><frame id = "15">0X000000000049D10B</frame></Stack></Record>

SELECT ring_buffer_type, timestamp, COUNT(ring_buffer_type) FROM sys.dm_os_ring_buffers

GROUP BY ring_buffer_type, timestamp

ORDER BY timestamp DESC

EDIT: My earlier assertion that you could transform timestamp into a DATETIME was wrong, I tested it on my own table and it came back with dates in 1989. Sorting by timestamp is better.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.