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.

Placing data and log files seperately

Hi, Gurus, I am running SQL-Server 2000 on SCSIIs with Raid-5. Three disks. I see only one Logical Disk with 3 partitions. Since it's recommended to have Log files seperate to Data files physicially, currently both exist in the D-Drive. How can it be accomplished?

Another question for ur kind suggestion. I have a merge-repl setup. Publisher and distributor at the same machine. One subscriber over the internet. Replication works through VPN rather that FTP. Whenever i apply SNAPSHOT it takes more that 12 hrs to replicate the 2 GB DB. Although VPN is more secured but the tunneling makes the replication rather slow i guess. Will there be a major difference if i open the default port of ftp on the ISA and change the subscriber to get the SNAPSHOT as Anonymous Subsciption. I mean What's better, Security VS Performance. During the SNAPSHOT the Web-users running on the Subscriber wait for the latest data which is very much annoying.

Another question please, I wanna replace the current publisher-DB machine with a new Hardware-Machine. I don't wanna lose Replication and re-running the SNAPSHOT. Is it possible to retain the publication by restoring the Master, Publication and Distributor respectively on the new machine. The new machine will have the same Operating system name and configuration.

Regards!!Howdy,

Buy another disk for your log if you want to go that way. As you have a RAID 5 array, all disks in the array will besame speed. The benefit of a separate disk for logs is that logs are written sequentially ( so faster ).
When you have new disk for logs, detach the database from the sevrer, move the log files ( .LDF ) to new log disk, reattach database with logs in the new location.

Security is worth the slow speed....especially if you get hacked. But, there may be a way to get data onto a secure FTP site.

Restore should be OK. Treat this like a disaster recovery test.

Cheers,

SG.|||Another question please, I wanna replace the current publisher-DB machine with a new Hardware-Machine. I don't wanna lose Replication and re-running the SNAPSHOT. Is it possible to retain the publication by restoring the Master, Publication and Distributor respectively on the new machine. The new machine will have the same Operating system name and configuration.

No problem refer to books online for 'Backing Up and Restoring Replication Databases' topic for more details.|||Thanx. I'll let u know if i face any problem while doing the restore.

Regards.

Placeholder Question...Simple (I think)

OK...all you gurus of SSRS I have a question that I think is simple but who knows...I have asked other questions that I thought were simple and it turned out it is very hard or impossible to do in SSRS 2005 version that I have.

Question: I have a Matix report that has a start date and an enddate parameter (among others). I need to do the following:

* Need to fill all columns that have no data with a value of zero

How in the world do you do this in Reporting Services 2005?

HELP!!!!

You could build this into your expression in your report using IIf() and IsNull() similar to the following:

IIf(IsNull(Fields!myfield.value), 0, Fields!myfield.value)

OR

You could build it into your SQL query similar to the following:

SELECT CASE WHEN IsNull(MyField) THEN 0 ELSE MyField END AS MyField FROM MYTABLE WHERE ......

|||

If I do this in SSRS...how do I nest a sum function?

Sum(Fields!Responses.Value)

|||

I would think you could do this:

=IIf(IsNull(Sum(Fields!myfield.value)), 0, Sum(Fields!myfield.value))

That is assuming that the value of the sum is in fact null.

|||Thank You!!!

|||You're welcome.

Pivot Query - Assigning Row Value Based on Column Name

Hey all,

i have a question for all the SQL Gurus out there. I have tried to think of a way around, it, but i cannot work it out.

I have a set of data: Samples Below:

Item Warehouse FOR1 FOR2 FOR3 FOR4 FOR5 FOR6 FOR7 FOR8 FOR9 FOR10 FOR11 FOR12 FOR13 FOR14

01-0001 010 329 329 335 343 317 331 328 331 31

I have written a Query to Pivot this data like below:

SELECT WAREHOUSE,ITEM, QTY

FROM

(SELECT ITEM,WAREHOUSE,FOR1,FOR2,for3,for4,for5,for6,for7,for8,for9,for10,
for11,for12,for13,for14,for15,for16,for17,for18,for19,for20,for21,
for22,for23,for24 FROM mvxreport.tbldmsForecasttoMovex) p

UNPIVOT

(QTY FOR tbldmsForecasttoMovex IN (FOR1,FOR2,for3,for4,for5,for6,for7,
for8,for9,for10,for11,for12,for13,for14,for15,for16,for17,for18,for19,
for20,for21,for22,for23,for24))AS unpvt

Warehouse Item Qty
010 01-0001 329
010 01-0001 329
010 01-0001 335
010 01-0001 343
010 01-0001 317
010 01-0001 331
010 01-0001 328
010 01-0001 331
010 01-0001 315
010 01-0001 344
010 01-0001 334
010 01-0001 321
010 01-0001 327
010 01-0001 328
010 01-0001 332
010 01-0001 342
010 01-0001 316
010 01-0001 330
010 01-0001 330
010 01-0001 331
010 01-0001 315
010 01-0001 343
010 01-0001 333
010 01-0001 322

I would like to add some more code to the query, so for each FOR% column,
i can put a numeric value in it. The value will be the numbers ,1 - 24 . One for each line as this represents Months Forward.

Example:

Warehouse Item Qty Month
010 01-0001 329 1
010 01-0001 329 2
010 01-0001 335 3
010 01-0001 343 4
010 01-0001 317 5
010 01-0001 331 6
010 01-0001 328 7
010 01-0001 331 8
010 01-0001 315 9
010 01-0001 344 10
010 01-0001 334 11
010 01-0001 321 12
010 01-0001 327 13
010 01-0001 328 14
010 01-0001 332 15
010 01-0001 342 16
010 01-0001 316 17
010 01-0001 330 18
010 01-0001 330 19
010 01-0001 331 20
010 01-0001 315 21
010 01-0001 343 22
010 01-0001 333 23
010 01-0001 322 24

Does anyone know how i can do this?

Many Thnank

Scotty

Use the below query,

Code Block

SELECT WAREHOUSE,ITEM, QTY, replace(monthfor,'For','') as [Month]

FROM

(SELECT ITEM,WAREHOUSE,FOR1,FOR2,for3,for4,for5,for6,for7,for8,for9,for10,

for11,for12,for13,for14,for15,for16,for17,for18,for19,for20,for21,

for22,for23,for24 FROM mvxreport.tbldmsForecasttoMovex) p

UNPIVOT

(QTY FOR monthfor IN (FOR1,FOR2,for3,for4,for5,for6,for7,

for8,for9,for10,for11,for12,for13,for14,for15,for16,for17,for18,for19,

for20,for21,for22,for23,for24))AS unpvt

|||

I think you can use ROW_NUMBER() function, something like that:

select Warehouse,Item, ROW_NUMBER() OVER (ORDER BY item) as Aqty

from

(

SELECT WAREHOUSE,ITEM, QTY

FROM

(SELECT ITEM,WAREHOUSE,FOR1,FOR2,for3,for4,for5,for6,for7,for8,for9,for10,
for11,for12,for13,for14,for15,for16,for17,for18,for19,for20,for21,
for22,for23,for24 FROM mvxreport.tbldmsForecasttoMovex) p

UNPIVOT

(QTY FOR tbldmsForecasttoMovex IN (FOR1,FOR2,for3,for4,for5,for6,for7,
for8,for9,for10,for11,for12,for13,for14,for15,for16,for17,for18,for19,
for20,for21,for22,for23,for24))AS unpvt

)

|||

thats awesome manivannan, once again you come to the rescue!!

thanks mate

Scotty

|||

recome,

select Warehouse,Item, ROW_NUMBER() OVER (ORDER BY item) as Month

from ...