Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Database Stack dump problem any ideas ?? Expand / Collapse
Author
Message
Posted Wednesday, January 02, 2008 6:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 1:27 AM
Points: 282, Visits: 1,070
Hi All,


I have noticed over the last couple of weeks that one of our most intensive databases starts to produce stack dumps. Below is the summary of the actual configuration

SQL 2005 SP2
Windows 2003 Server
32GB of memory, AWE configured to use 24GB

The problem is that I am not very sure if the AWE is actually helping us or is the one causing problems, the issue appears to be memory problems, but I’m not sure of how to diagnose if we have a memory problem.
What normally happens is that we have a procedure which runs from time to time, blocks other processes on the server, and when we eventually decide to kill it, rollback takes forever, then we end up rebooting the server, after rebooting, there are loads of stack dump errors in the error log.

Anyone has any idea on this problem ?

Post #437939
Posted Wednesday, January 02, 2008 8:47 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 6:12 AM
Points: 4,432, Visits: 4,156
If you get stack dumps on a regular base you should contact Microsoft PSS and open a support incident. The dump might show (part of) the last query which caused the dump, but even then it can be a combination of factors.

The problem could be memory related but it could also be something totally different.


Markus Bohse
Post #438013
Posted Wednesday, January 02, 2008 8:54 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 1:27 AM
Points: 282, Visits: 1,070
Hi

Thanks for the post, you are right. sometimes one can see the query within the stack dump, one theory could be that the code is broken, but this has been running for years without any problems and hasn't changed over the years..

Also, the Microwst Pss you talked about, does one have to pay for this service ?

Cheers
Post #438016
Posted Wednesday, January 02, 2008 10:09 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 9:43 AM
Points: 1,098, Visits: 6,419
Unfortunately you don't get them free.

Depending on your support contract you may get a certain number of support calls per year before they become chargable.

Check this link out.

http://support.microsoft.com/default.aspx?scid=fh;EN-US;offerprophone&sd=tech#faq607
Post #438051
Posted Thursday, January 03, 2008 2:18 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, March 14, 2008 4:50 AM
Points: 64, Visits: 103
Hi Dean,

While your trying to get an ms contract you could have a look in the logs folder and see if you have any SQLDumpXXX.txt files and post a couple of the newest ones - if the server has sentitive data like credit cards in a db then have a read through first!!

Ed
Post #438282
Posted Thursday, January 03, 2008 4:20 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 1:27 AM
Points: 282, Visits: 1,070
Hi All,

Thanks for the replies.

Below is the detail of the dump log file you asked for


• 2007-12-23 10:37:13.02 spid2s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [H:\Production\CompanyProfile\CompanyProfile_200712.ndf] in database [CompanyProfile] (12). The OS file handle is 0x000008B8. The offset of the latest long I/O is: 0x000025fe4e2000
• 2007-12-23 10:37:13.02 spid2s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [H:\Production\CompanyProfile\FusionReference.ndf] in database [CompanyProfile] (12). The OS file handle is 0x00000894. The offset of the latest long I/O is: 0x00000040b1c000
• 2007-12-23 10:37:13.02 spid2s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [H:\Production\CompanyProfile\Fusion.ndf] in database [CompanyProfile] (12). The OS file handle is 0x00000890. The offset of the latest long I/O is: 0x00000039eac000
• 2007-12-23 10:37:13.02 spid2s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [H:\Production\CompanyProfile\FusionMain.mdf] in database [CompanyProfile] (12). The OS file handle is 0x00000864. The offset of the latest long I/O is: 0x00000006526000
• 2007-12-23 10:37:23.02 spid2s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\MSSQL\MSDBLog.ldf] in database [msdb] (4). The OS file handle is 0x00000880. The offset of the latest long I/O is: 0x00000000979000
• 2007-12-23 10:38:39.90 Server Using 'dbghelp.dll' version '4.0.5'
• 2007-12-23 10:41:48.71 spid87 A time-out occurred while waiting for buffer latch -- type 2, bp 051A6AB8, page 10:19922697, stat 0x7c0040d, database id: 12, allocation unit Id: 484272212956179/202795456246057, task 0x00C143E8 : 7, waittime 300, flags 0x1a, owning task 0x00DECD48. Not continuing to wait.
• 2007-12-23 10:42:40.46 spid71 A time-out occurred while waiting for buffer latch -- type 2, bp 0C0C9424, page 1:12949, stat 0x4c1010f, database id: 12, allocation unit Id: 327680/281474977038336, task 0x008DBA68 : 0, waittime 300, flags 0x1a, owning task 0x00C14208. Not continuing to wait.
• 2007-12-23 10:46:24.32 spid2s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\MSSQL\mssqlsystemresource.mdf] in database [mssqlsystemresource] (32767). The OS file handle is 0x00000600. The offset of the latest long I/O is: 0x000000005e0000
• 2007-12-23 10:46:24.32 spid2s SQL Server has encountered 37 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [H:\Production\CompanyProfile\CompanyProfile_200712.ndf] in database [CompanyProfile] (12). The OS file handle is 0x000008B8. The offset of the latest long I/O is: 0x0000252b198000
• 2007-12-23 10:46:24.32 spid2s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\MSSQL\master.mdf] in database [master] (1). The OS file handle is 0x000005D4. The offset of the latest long I/O is: 0x00000000178000
• 2007-12-23 10:46:24.34 Server ***Unable to get thread context - no pss
• 2007-12-23 10:46:24.36 Server * *******************************************************************************
• 2007-12-23 10:46:24.36 Server *
• 2007-12-23 10:46:24.36 Server * BEGIN STACK DUMP:
• 2007-12-23 10:46:24.36 Server * 12/12/07 10:46:24 spid 0
• 2007-12-23 10:46:24.36 Server *
• 2007-12-23 10:46:24.36 Server * Non-yielding Scheduler
• 2007-12-23 10:46:24.36 Server *
• 2007-12-23 10:46:24.36 Server * *******************************************************************************
• 2007-12-23 10:46:24.39 Server Stack Signature for the dump is 0x00000261
Post #438312
Posted Thursday, January 03, 2008 4:28 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, March 14, 2008 4:50 AM
Points: 64, Visits: 103
do you have any SQLDmpr####.mdmp files in your sql logs directory? Can you attach one or pm it?
Post #438313
Posted Thursday, January 03, 2008 4:31 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 9:43 AM
Points: 1,098, Visits: 6,419
Have you monitored perfmon? To start with, I'd look at the Physical Disk IO counters.

Does this happen all of the time or do you get periods between errors where performance is fine? If so, thats a good baseline to see when the errors occur in perfmon.

Are these SAN attached disks?
Post #438315
Posted Thursday, January 03, 2008 4:57 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 21, 2013 7:25 AM
Points: 77, Visits: 253
Dean Jones (1/3/2008)
Hi All,

Thanks for the replies.

Below is the detail of the dump log file you asked for


• 2007-12-23 10:37:13.02 spid2s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [H:\Production\CompanyProfile\CompanyProfile_200712.ndf] in database [CompanyProfile] (12). The OS file handle is 0x000008B8. The offset of the latest long I/O is: 0x000025fe4e2000
• 2007-12-23 10:37:13.02 spid2s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [H:\Production\CompanyProfile\FusionReference.ndf] in database [CompanyProfile] (12). The OS file handle is 0x00000894. The offset of the latest long I/O is: 0x00000040b1c000
• 2007-12-23 10:37:13.02 spid2s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [H:\Production\CompanyProfile\Fusion.ndf] in database [CompanyProfile] (12). The OS file handle is 0x00000890. The offset of the latest long I/O is: 0x00000039eac000
• 2007-12-23 10:37:13.02 spid2s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [H:\Production\CompanyProfile\FusionMain.mdf] in database [CompanyProfile] (12). The OS file handle is 0x00000864. The offset of the latest long I/O is: 0x00000006526000
• 2007-12-23 10:37:23.02 spid2s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\MSSQL\MSDBLog.ldf] in database [msdb] (4). The OS file handle is 0x00000880. The offset of the latest long I/O is: 0x00000000979000
• 2007-12-23 10:38:39.90 Server Using 'dbghelp.dll' version '4.0.5'
• 2007-12-23 10:41:48.71 spid87 A time-out occurred while waiting for buffer latch -- type 2, bp 051A6AB8, page 10:19922697, stat 0x7c0040d, database id: 12, allocation unit Id: 484272212956179/202795456246057, task 0x00C143E8 : 7, waittime 300, flags 0x1a, owning task 0x00DECD48. Not continuing to wait.
• 2007-12-23 10:42:40.46 spid71 A time-out occurred while waiting for buffer latch -- type 2, bp 0C0C9424, page 1:12949, stat 0x4c1010f, database id: 12, allocation unit Id: 327680/281474977038336, task 0x008DBA68 : 0, waittime 300, flags 0x1a, owning task 0x00C14208. Not continuing to wait.
• 2007-12-23 10:46:24.32 spid2s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\MSSQL\mssqlsystemresource.mdf] in database [mssqlsystemresource] (32767). The OS file handle is 0x00000600. The offset of the latest long I/O is: 0x000000005e0000
• 2007-12-23 10:46:24.32 spid2s SQL Server has encountered 37 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [H:\Production\CompanyProfile\CompanyProfile_200712.ndf] in database [CompanyProfile] (12). The OS file handle is 0x000008B8. The offset of the latest long I/O is: 0x0000252b198000
• 2007-12-23 10:46:24.32 spid2s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\MSSQL\master.mdf] in database [master] (1). The OS file handle is 0x000005D4. The offset of the latest long I/O is: 0x00000000178000
• 2007-12-23 10:46:24.34 Server ***Unable to get thread context - no pss
• 2007-12-23 10:46:24.36 Server *


This is prolem with you hardware,

It's not a SQL Server problem. download and run the SQLIOSim tool (see http://blogs.msdn.com/sqlserverstorageengine/archive/2006/10/06/SQLIOSim-available-for-download.aspx) to stress the IO subsystem and see what it throws up.

This error was introduced after SP4 and is an FYI more than a sql error ..

Kevin Brennan M.Sc MCDBA, MCSE, MCP+I




Kevin Brennan M.Sc, MCITP, MCDBA, MCSE, MCTS,MCP+I
Post #438323
Posted Thursday, January 03, 2008 5:05 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 6:12 AM
Points: 4,432, Visits: 4,156
Kevin is right about the IO messages which take too long, but in my experience these messages don't automatically lead to a stack dump. I remember one case like this, there the batteries on the IO controller went dead and because of some HP firmware setting, the controller didn't use it's write cache any longer. Maybe worth checking too.


Markus Bohse
Post #438325
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse