Please help me debug Hex Dumps

Hello gurus,

Our SQL Servers is giving us a headache, after a certain period in time, either SQL Service automatically shuts down by itself or hangs. I've opened the logs and found hex dumps. Can you help me out with these?

2007-07-08 04:04:35.20 spid53 SqlDumpExceptionHandler: Process 1760 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
* ************************************************** *****************************
*
* BEGIN STACK DUMP:
* 07/08/07 04:04:35 spid 53
*
* Exception Address = 0042D46D
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 6AF1EDF0
* Input Buffer 4088 bytes -
* USE Document DBCC DBREINDEX (dtproperties) DBCC DBREINDEX (REfID_DocID
* ) DBCC DBREINDEX (RF_UNISYS_ErrorCodes) DBCC DBREINDEX (SYS_Document_F
* lat_Meta_Data) DBCC DBREINDEX (SYS_Document_Meta_Data) DBCC DBREINDEX
* (SYS_Document_Meta_Detail) DBCC DBREINDEX (SYS_Documents) DBCC DBREIND
* EX (SYS_ETFuelType) DBCC DBREINDEX (SYS_ETStatus) DBCC DBREINDEX (SYS_
* HF_Document_Meta_Data) DBCC DBREINDEX (SYS_HF_MI_Emission_Results) DBC
* C DBREINDEX (SYS_ITP_Failed) DBCC DBREINDEX (SYS_MI_Emission_Results)
* DBCC DBREINDEX (SYS_RF_Document_Meta_Data) DBCC DBREINDEX (SYS_RF_Docum
* ent_Status) DBCC DBREINDEX (SYS_TR_Document) DBCC DBREINDEX (SYS_TR_SM
* S_Document) USE Industry DBCC DBREINDEX (dtproperties) DBCC DBREIND
* EX (IND_MF_Industry) DBCC DBREINDEX (RF_ErrorCodes) DBCC DBREINDEX (RF
* _OperationCodes) DBCC DBREINDEX (RF_Unisys_ErrCodes) DBCC DBREINDEX (S
* YS_Admin_Has_Agents) DBCC DBREINDEX (SYS_Admin_Sharing) DBCC DBREINDEX
* (SYS_Companies) DBCC DBREINDEX (SYS_Company_Has_Admins) DBCC DBREINDE
* X (SYS_Company_Meta_Data) DBCC DBREINDEX (SYS_HF_Admin_Has_Agents) DBC
* C DBREINDEX (SYS_HF_Companies) DBCC DBREINDEX (SYS_HF_Company_Has_Admin
* s) DBCC DBREINDEX (SYS_HF_Company_Meta_Data) DBCC DBREINDEX (SYS_HF_Us
* er_Meta_Data) DBCC DBREINDEX (SYS_HF_Users) DBCC DBREINDEX (SYS_MF_App
* Variables) DBCC DBREINDEX (SYS_MI_Token) DBCC DBREINDEX (SYS_Page_Acce
* ss) DBCC DBREINDEX (SYS_Pages) DBCC DBREINDEX (SYS_Password_History)
* DBCC DBREINDEX (SYS_RF_Announcement) DBCC DBREINDEX (SYS_RF_BodyType)
* DBCC DBREINDEX (SYS_RF_Color) DBCC DBREINDEX (SYS_RF_Company_Meta_Data)
* DBCC DBREINDEX (SYS_RF_Company_Status) DBCC DBREINDEX (SYS_RF_DieselT
* ype) DBCC DBREINDEX (SYS_RF_EmissionFees) DBCC DBREINDEX (SYS_RF_Emiss
* ionRules) DBCC DBREINDEX (SYS_RF_Fuel_Type) DBCC DBREINDEX (SYS_RF_Hel
* pDetails) DBCC DBREINDEX (Sys_RF_Make) DBCC DBREINDEX (SYS_RF_Month)
* DBCC DBREINDEX (SYS_RF_MVClassification) DBCC DBREINDEX (SYS_RF_MVType)
* DBCC DBREINDEX (SYS_RF_MVType2) DBCC DBREINDEX (SYS_RF_Page_Groups)
* DBCC DBREINDEX (SYS_RF_Purpo
*
*
* MODULE BASE END SIZE
* sqlservr 00400000 00CBAFFF 008bb000
* Invalid Address 77F80000 77FFBFFF 0007c000
... <snipped>
* xpstar 09240000 09248FFF 00009000
* rsabase 092D0000 092F2FFF 00023000
* dbghelp 0AA80000 0AB7FFFF 00100000
*
* Edi: 0AA53937: 00000000 00000000 00000000 84004A00 98018901 C501B101
* Esi: 6AF1EDF0:
* Eax: 00000878:
* Ebx: FFFFE000:
* Ecx: 3FFFF800:
* Edx: FFFFE000:
* Eip: 0042D46D: CA8BA5F3 F303E183 DC7D8BA4 83D045FF 4D8B2CC7 E9D233E0
* Ebp: 0A1BFCC0: 0A1BFCE4 0042D5CD 71E428CC 71E40570 71E40988 0AA519A0
* SegCs: 0000001B:
* EFlags: 00010206: 00530053 0052004F 003D0053 00000034 0053004F 0057003D
* Esp: 0A1BFC28: 0AA519A0 71E4052C 00000000 00000010 71E408A0 00000010
* SegSs: 00000023:
* ************************************************** *****************************
* ------------------------
* Short Stack Dump
* 0042D46D Module(sqlservr+0002D46D)
* 0042D5CD Module(sqlservr+0002D5CD)
* 0042D6C7 Module(sqlservr+0002D6C7)
* 00508750 Module(sqlservr+00108750)
* 0051EB18 Module(sqlservr+0011EB18)
* 0051E9E4 Module(sqlservr+0011E9E4)
* 0085EACA Module(sqlservr+0045EACA) (GetIMallocForMsxml+0006A08A)
* 004229A7 Module(sqlservr+000229A7)
* 0087B87B Module(sqlservr+0047B87B) (GetIMallocForMsxml+00086E3B)
* 0087E3C9 Module(sqlservr+0047E3C9) (GetIMallocForMsxml+00089989)
* 0059A449 Module(sqlservr+0019A449)
* 41075309 Module(ums+00005309) (ProcessWorkRequests+000002D9 Line 456+00000000)
* 41074978 Module(ums+00004978) (ThreadStartRoutine+00000098 Line 263+00000007)
* 7C34940F Module(MSVCR71+0000940F) (endthread+000000AA)
* 7C57B3BC Module(KERNEL32+0000B3BC) (lstrcmpiW+000000B7)
* ------------------------
*Dump thread - spid = 53, PSS = 0x55a35280, EC = 0x55a355b0
*Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL\log\SQLDump0020.txt

*** Problems occur intermittently, sometimes after our full backup which occurs every 0000h, sometimes 30 minutes after our transaction log dumps.

Please advice me on my next step. ThanksProbably need the latest service packs:
http://support.microsoft.com/kb/q293292/|||May also be a plain ol' memory or disk issue. Have you checked the hardware?|||hi blindmin: yes, we've installed the latest service packs both for our OS (windows 2000 server sp4) and rdbms (sql server 2000 sp4)

ReadySetStop: Memory for one, might serve as culprit. our box is running under DELL PowerEdge 1955 (blade server). memory is around 8GB (6gb goes to SQL, 2 GB goes to OS). There was a time when we ported from one blade to another blade server because of faulty OS. Before, when SQL encounters errors, the whole thing just freezes up. Now, SQL has the ability to shoot out hex dumps. That's why I need to have a basis before pointing it directly to a hardware fault.|||these were not the only hex dumps i've encountered. There were some hex dumps that has some variable declaration and value assigning like declare @.asdf datetime etc and lots of hex equivalents on the side. So, it might not only be DBCC stuffs. Just don't know where to start|||If the errors is not related only to DBCC, then I too would look at bad memory as the next possible culprit.|||just an update, we've managed to consult Microsoft for these errors. Initially, they told us that there's an issue having /PAE enabled. I'll keep you guys posted.|||just an update guys, got this from MS tech support.

Hi

I noticed the two SQL Servers have AWE enabled, while the platform is Windows 2000 (Build 2195: Service Pack 4). We have a known issue on such environment, with the similar dump call stacks. Please refer to the following URL.

Access violations when you use the /PAE switch in Windows 2000

http://support.microsoft.com/kb/838647

You may notice unpredictable behavior on a multiprocessor computer that is running SQL Server 2000 and has the Physical Addressing Extensions (PAE) specification enabled

http://support.microsoft.com/kb/838765/en-us

To avoid the issue, could you please upgrade your Windows 2000 to Rollup 1 for Microsoft Windows 2000 Service Pack 4? For more information about the Rollup, please refer to:



http://support.microsoft.com/kb/891861/en-us

And it can be downloaded from:

http://www.microsoft.com/downloads/details.aspx?FamilyId=B54730CF-8850-4531-B52B-BF28B324C662&displaylang=en

After applied it, please keep monitoring your SQL Server. If the issue reoccurs, please send me your SQL Server errorlog files with the new dump file generated.

Please help

I have a certain problem that I'm going to try and demonstrate using the northwind
database as an example.

I'm trying to write a stored procedure in SQL Server 2000, I am using the products table and
the order details table.

The first procedure I wrote uses a left outer join to show all the products and if the product
doesn't show up in the order details table it will show up with a zero no problem the stored procedure
is as follows and works great.

ALTER PROCEDURE dbo.StoredProcedure1
AS
SELECT dbo.Products.ProductID, dbo.Products.ProductName, COUNT(dbo.[Order Details].ProductID) AS [in orders]

FROM dbo.Products LEFT OUTER JOIN
dbo.[Order Details] ON dbo.Products.ProductID = dbo.[Order Details].ProductID

GROUP BY dbo.Products.ProductID, dbo.Products.ProductName

Now I change the stored procedure to filter the second table by an orderid and now the only products
that show up are the products that exist in the second table.

My question is how do I show all the products like in the first stored procedure
and filter the stored procedures second table and still show all the products
including the ones that don't show up.

I wrote the second stored procedure and like I said it only shows the products that show up in the second table.

ALTER PROCEDURE dbo.StoredProcedure2
AS
SELECT dbo.Products.ProductID, dbo.Products.ProductName, COUNT(dbo.[Order Details].ProductID) AS [in orders]

FROM dbo.Products LEFT OUTER JOIN
dbo.[Order Details] ON dbo.Products.ProductID = dbo.[Order Details].ProductID

GROUP BY dbo.Products.ProductID, dbo.Products.ProductName, dbo.[Order Details].OrderID

HAVING (dbo.[Order Details].OrderID = 10248)

I would really appreciate if somebody codes demonstrate to me how to do this
thanks in advance.This is how it hsould be:

SELECT dbo.Products.ProductID, dbo.Products.ProductName,COUNT(dbo.[Order Details].ProductID)AS[in orders]
FROM dbo.ProductsLEFTOUTERJOIN
dbo.[Order Details]ON dbo.Products.ProductID= dbo.[Order Details].ProductID
WHERE dbo.[Order Details].OrderID= 10248
GROUPBY dbo.Products.ProductID, dbo.Products.ProductName, dbo.[Order Details].OrderID

|||Thank you very much for your quick response ndinakar,
but the procedure you wrote gives the same result as the second procedure
I wrote.

What I wanted to do was show all the products that exist in the products table
like in the first procedure which shows all the products and products that don't show up
in the second table show up with a zero I would like to do the same thing but when I filter
the second table by the orderId the only result I get is the products that show up in the second table not all the products.

How can I show all the products from the products table
and still filter the productID in the second table|||Of course it only shows products from the second table. Your WHERE clause tells it to only show specific products from the second table.

Your question makes little sense...|||I showed have explained what I was trying to do in the first place,

I've written a program in ASP.NEV VB I have a drop-down list in a data grid
the drop-down list contains instructors names and the value contains their ID

The drop-down list data source is the instructors table each time and instructor makes a dive
it goes into the orders table by the instructors ID.

What I was trying to do is get a list of all the instructors and their ID and see how many times they show up
in the orders table for certain dates and arrange the drop-down list according to the amount of dive's
the instructor made during that date.

----------Instructors table----------

Inst_Idint40
Inst_Full_Namenvarchar500
Activebit10

-------Orders Table --------------

Order_Idint40
Activity_Idint40
Inst_Idint41
Client_Namenvarchar500
Telnvarchar301
Paymentint40
Voucher_Nonvarchar501
Agent_Idint41
Reservation_Noint41
Worker_Idint41
Order_Date_Timedatetime80
Equipmentnvarchar2|||Try something like:

SELECT dbo.Products.ProductID, dbo.Products.ProductName, ISNULL(COUNT(dbo.[Order Details].ProductID),0 AS [in orders]
FROM dbo.Products LEFT OUTER JOIN
dbo.[Order Details] ON dbo.Products.ProductID = dbo.[Order Details].ProductID
GROUP BY dbo.Products.ProductID, dbo.Products.ProductName
ORDER BY (3)

This will give you a 0 value for products with no corresponding orders.

Does this help, or am I still misunderstanding the requirements?|||OK, I see what I was missing. That OrderDate requirement.

No problem. Just do your OUTER JOIN to an inline table (or subquery).

LEFT OUTER JOIN
 (SELECT ProductID, DetailID
 FROM [Order Details]
 WHERE OrderDate = @.OrderDate) AS OrderDetails

Now just treat your virtual OrderDetails table the same as you would your real OrderDetails table. The rest of the query should be the same.|||I didn't exactly understand how to do it I am quite new to SQL Server
I tried copying your script like so

CREATE PROCEDURE dbo.StoredProcedure1
AS
SELECT dbo.Products.ProductID, dbo.Products.ProductName, COUNT(dbo.[Order Details].ProductID) AS [in orders]
FROM dbo.Products LEFT OUTER JOIN
(SELECT ProductID, DetailID
FROM [Order Details]
WHERE OrderDate = @.OrderDate) AS OrderDetails

GO

But it did not work i probably don't understand how to make an inline table
if you could just give me a little more help thanks a lot|||SELECT dbo.Products.ProductID, dbo.Products.ProductName, COUNT(OrderDetails.DetailID) AS InOrders
FROM dbo.Products LEFT OUTER JOIN
(SELECT ProductID, DetailID
FROM [Order Details]
WHERE OrderDate = @.OrderDate) AS OrderDetails
GROUP BY dbo.Products.ProductID, dbo.Products.ProductName
ORDER BY (3)|||I tried using the procedure you wrote and then tried it with my actual table
and each time I get the same error incorrect Syntex neither the keywords 'group'
do you have any suggestions but I've also attached my actual procedure.

---------------------------

ALTER PROCEDURE dbo.GetInstructors
(@.Id int)
AS

SELECT dbo.Instructors.Inst_Id, dbo.Instructors.Inst_Full_Name, COUNT(dbo.Orders_Sub.Inst_Id) AS Expr1
FROM dbo.Instructors LEFT OUTER JOIN
(SELECT dbo.Orders_Sub.Inst_Id
FROM dbo.Orders_Sub
WHERE dbo.Orders_Sub.Order_Id = @.Id) AS Orders_Sub
GROUP BY dbo.Instructors.Inst_Id, dbo.Instructors.Inst_Full_Name
ORDER BY (3)

---------------------------

I really appreciate the help you giving me thank you in advance again.|||Sorry. I left out the join condition:

FROM dbo.Instructors LEFT OUTER JOIN
(SELECT dbo.Orders_Sub.Inst_Id
FROM dbo.Orders_Sub
WHERE dbo.Orders_Sub.Order_Id = @.Id) AS Orders_Sub
ON dbo.Instructors.Inst_Id = Orders_Sub.Inst_Id

|||Thank you very much for your help it works like a dream.

pivot table % and $ Formatting ?

Hi Guys

Just wondering after I've formatted certain facts say ie. Format String: Percentage and Format String: Currency all values are formatted correctly in AS however when I load the data in Excel pivot table all formatting is lost.

Is there anyway that I can allow formatting in pivot table ?

Thanks
TomSame problem here, and same interrogation.

Antoine.

pivot table % and $ Formatting ?

Hi Guys

Just wondering after I've formatted certain facts say ie. Format String: Percentage and Format String: Currency all values are formatted correctly in AS however when I load the data in Excel pivot table all formatting is lost.

Is there anyway that I can allow formatting in pivot table ?

Thanks
TomSame problem here, and same interrogation.

Antoine.