External dump process return code 0x20000001.

Last Post 06 Aug 2009 05:46 AM by Shafiq. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Shafiq
New Member
New Member

--
05 Aug 2009 02:03 PM
I have web application that accesses data from SQL Server. SQL Server access the data from AS400 and put it in a temporary table (using @declare table ) statement. Then I join AS400 & SQL Server data to return a results set. It works fine but after couple of days I get this dump. Then I can't able to connect to the server. Only solution is to restart the server.

I run DBCC CHECKDB (dbname) WITH ALL_ERRORMSGS, NO_INFOMSGS
Nothing get returned

I am using Microsoft SQL Server Standard Edition 2005 SP2 (9.00.3077.00)

Here is some info regarding the error. Please Guide

Thanks
Shafiq

New queries assigned to process on Node 0 have not been picked up by a worker thread in the last 180 seconds. Blocking or long-running queries can contribute to this condition, and may degrade client response time. Use the "max worker threads" configuration option to increase number of allowable threads, or optimize current running queries. SQL Process Utilization: 0%. System Idle: 98%.

The time stamp counter of CPU on scheduler id 1 is not synchronized with other CPUs.

New queries assigned to process on Node 0 have not been picked up by a worker thread in the last 60 seconds. Blocking or long-running queries can contribute to this condition, and may degrade client response time. Use the "max worker threads" configuration option to increase number of allowable threads, or optimize current running queries. SQL Process Utilization: 0%. System Idle: 99%.

External dump process return code 0x20000001.
External dump process returned no errors.
Stack Signature for the dump is 0x0000005F
* Short Stack Dump
* *******************************************************************************
* Deadlocked Schedulers
* 08/05/09 16:32:10 spid 0
* BEGIN STACK DUMP:
* *******************************************************************************
***Stack Dump being sent to D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0081.txt
**Dump thread - spid = 0, PSS = 0x00000000, EC = 0x00000000
Using 'dbghelp.dll' version '4.0.5'
Shafiq
New Member
New Member

--
06 Aug 2009 05:46 AM
It's possible that multiple users are requesting the data at the same time.

CREATE Proc [dbo].[ListSales]
( @Date numeric(8,0)=0 )
As
Begin

Set NoCount On
DECLARE @PollDate datetime

DECLARE @temp TABLE (
[ID] [int] IDENTITY(1,1) NOT NULL,
[TRNDATE] [numeric](8, 0) NOT NULL,
[STORENUM] [smallint] NOT NULL,
[Register] [smallint] NOT NULL,
[SALES] [numeric](11, 2) NOT NULL,
[PCS] [int] NOT NULL,
[TRANS] [int] NOT NULL,
[TOTALTRANS] [int] NOT NULL);


Begin Try

If @Date = 0
Begin
--Print 'AS400 Query'
Set @Date = Cast(Convert(varchar(8),Getdate(),112) as numeric(8,0))
Insert into @temp ([TRNDATE],[STORENUM],[Register],[SALES],[PCS], [TRANS], [TOTALTRANS])
Exec ('Call Catalogue.StoredProc (?)',@Date) AT AS400;

End
Else
Begin
--Print 'SQL Query'
Insert into @temp ([TRNDATE],[STORENUM],[Register],[SALES],[PCS], [TRANS], [TOTALTRANS])
Select TRNDATE, STORENUM, Register, SALES, PCS, TRANS, TOTALTRANS
From dbo.Summary
Where TRNDATE = @Date

End;


With CTE As
(
Select
s.District, d.DMName, tmp.TrnDate,
Sum(Sales) as Sales,Sum(Pcs) as Pcs, Sum(Trans) as Trans, Sum(TotalTrans) as TotalTrans from @temp tmp
inner Join stores.dbo.store s on temp.StoreNum = s.StoreNum
inner Join stores.dbo.District d ON s.District = d.DistrictID
Group by s.District, d.DMName, tmp.TrnDate ),
temp_Conversion As (
Select District, DMName, TrnDate, Sales,Pcs,Trans,TotalTrans,
Case TotalTrans When 0 Then 0 Else Cast(Trans/Cast(TotalTrans as decimal(7,0)) as decimal(9,4)) end as Conversion From temp )
Select * from temp_Conversion
Order by 2;
End Try
Begin Catch
Insert into dbo.ErrorLog (ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure, ErrorLine, ErrorMessage)
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE () as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;

End Catch

End

Dump is pasted below:

=====================================================================
BugCheck Dump
=====================================================================

This file is generated by Microsoft SQL Server
version 9.00.3077.00
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.
Bios Version is DELL - 1
Phoenix ROM BIOS PLUS Version 1.10 A21
Current time is 16:32:10
Shafiq
New Member
New Member

--
07 Aug 2009 07:23 AM
SP2,

Commit mode is Commit Immediate(*None) in ODBC-DSN using AS400.

Shafiq
New Member
New Member

--
11 Aug 2009 08:20 AM
When you create linked server with AS400, using ODBC-DSN, You can specify the commit mode in odbc-dsn settings.

When this error occurred, either sql restart or server reboot solves this probem. But problem come back after 3 to 4 days.

Thanks
Shafiq
Shafiq
New Member
New Member

--
03 Sep 2009 11:20 AM
Do you have to pay to use MS PSS services?
You are not authorized to post a reply.

Acceptable Use Policy