Retrospective spid investigation

Question

Retrospective spid investigation

John Reynolds-488748

SSC Rookie

Points: 28
More actions
September 17, 2008 at 5:11 pm

#198291

Hello,
I've inherited the maintenance of a client's SQL Server system which consists mainly of coming in once a week and kicking the tyres and changing the oil as the system tend to tick along pretty well on its own.
This, however, week I found the following entry in the error logs:
spid23 Error: 0, Severity: 19, State: 0
There is another entry for the same spid which lists details of an exception error which has been written out to a log file:
SqlDumpExceptionHandler: Process 213 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
* *******************************************************************************
*
* BEGIN STACK DUMP:.....
(I won't list the whole thing as its quite large).
The database in question serves a large-ish organisation so the likelyhood of being able to find someone who may have had an issue with an applciation at some point a number of days ago (and who can remember what they were doing at the time) is pretty remote so I was wondering if is a way to track down what spid23 was doing at the time the exception was thrown?
Many thank in advance,
JR

Viewing 7 posts - 1 through 6 (of 6 total)

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

bkDBA Ten Centuries Points: 1320 More actions · Answer 1

The lines in your log just above what you posted might give a clue. Can you post the prior 10 or so lines, and also show the timestamps? Also, if you check your activity now, does spid 23 still exist as a system process?

_______________
bkDBA
0.175 seconds -- 10 year average margin of victory at the Daytona 500
212 Degrees

John Reynolds-488748 SSC Rookie Points: 28 More actions · Answer 2

Thanks for the response. The incident happened a couple of days ago, so the spid (which was spid213 not 23 as I said initially) is gone.

Here's the log from the beginning:

=====================================================================

BugCheck Dump

=====================================================================

This file is generated by Microsoft SQL Server 8.00.760

upon detection of fatal unexpected error. Please return this file,

the query or program that produced the bugcheck, the database and

the error log, and any other pertinent information with a Service Request.

Computer type is AT/AT COMPATIBLE.

Current time is 11:03:57 09/17/08.

4 Intel x86 level 6, 701 Mhz processor(s).

Windows NT 5.0 Build 2195 CSD Service Pack 4.

Memory

MemoryLoad = 79%

Total Physical = 3743 MB

Available Physical = 750 MB

Total Page File = 7684 MB

Available Page File = 4649 MB

Total Virtual = 3071 MB

Available Virtual = 276 MB

*Stack Dump being sent to G:\Program Files\Microsoft SQL Server\MSSQL\log\SQLDu

mp0093.txt

* *****************************************************************************

**

*

* BEGIN STACK DUMP:

* 09/17/08 11:03:57 spid 213

*

* Exception Address = 00403ACB (RecBase::Resize + 00000005 Line 0+00000000)

* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION

bkDBA Ten Centuries Points: 1320 More actions · Answer 3

Thanks. Could you send the lines above and below this line:

[font="Courier New"]EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process[/font]

For what it's worth, updating your Service Pack might prevent this in the future. Your log indicates you have SQL Server Service Pack 3 (8.00.760). There is a known issue relating to a "c0000005 EXCEPTION_ACCESS_VIOLATION" that was fixed in SQL Server Service Pack 4: http://support.microsoft.com/kb/892451

_______________
bkDBA
0.175 seconds -- 10 year average margin of victory at the Daytona 500
212 Degrees

John Reynolds-488748 SSC Rookie Points: 28 More actions · Answer 4

I've (hopefully) attached the entire log file.

Thanks for the info re. the service pack, I'll mention it to the client and see what they want to do.

bkDBA Ten Centuries Points: 1320 More actions · Answer 5

Thanks for the dump file. I was actually wanting to see the SQL log file that contains the lines for spid213. If you look there, that might give you some information as to what SQL query or sp was running at the time.

_______________
bkDBA
0.175 seconds -- 10 year average margin of victory at the Daytona 500
212 Degrees

John Reynolds-488748 SSC Rookie Points: 28 More actions · Answer 6

Oops, sorry.

I won't be back on the client site until next week and remote access is a little sketchy, so I'll grab the log entries next tie I'm there.

Thanks for your help thus-far.

JR