Database consistency problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

Database consistency problem

Author

Topic

aashirwad
Starting Member

9 Posts

Posted - 2008-07-16 : 11:57:06

I have a DTS package which extracts data from multiple tables and inserts into a SQL Server 2000 database. The version of my database is Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1).

I have this particular table while my DTS package inserts records, it inserts around 16000 records and then I get the following error message.
"Error: 7987, Severity: 22, State: 1
A possible database consistency problem has been detected on database <db_name>. DBCC CHECKDB and DBCC CHECKCATALOG should be run on database <db_name>.

As the above database is an intermediate database, this database gets cleared or truncated every night. So for me the data here is not important...Hence I created another new database on the same box and tried to run the above DTS package. For my surprise, the same error shows up even on this new database and at the same time while inserting into the same table. Can somebody help me to fix this? Help will be sincerely appreciated. Thanks.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2008-07-16 : 13:13:23

This is SQL Server saying it's discovered corruption while doing a select or update and giving a nice error instead of asserting. See http://support.microsoft.com/kb/828337.

It basically means that the databases you're creating are getting corrupted in some way - most likely something in the IO subsystem. Can you run DBCC CHECKDB (dbname) WITH ALL_ERRORMSGS, NO_INFOMSGS and post the output?

Thanks

Paul Randal
SQL Server MVP, Managing Director, SQLskills.com

aashirwad
Starting Member

9 Posts

Posted - 2008-07-16 : 14:21:28

quote:
Originally posted by paulrandal

This is SQL Server saying it's discovered corruption while doing a select or update and giving a nice error instead of asserting. See http://support.microsoft.com/kb/828337.

It basically means that the databases you're creating are getting corrupted in some way - most likely something in the IO subsystem. Can you run DBCC CHECKDB (dbname) WITH ALL_ERRORMSGS, NO_INFOMSGS and post the output?

Thanks

Paul Randal
SQL Server MVP, Managing Director, SQLskills.com

aashirwad
Starting Member

9 Posts

Posted - 2008-07-16 : 14:32:55

Thanks Paul!

I ran DBCC CHECKDB (dbname) WITH ALL_ERRORMSGS, NO_INFOMSGS on all the databases in the SQL Server Instance including the database where I am having this problem. It does not show any error message and tells "The command(s) completed successfully.".

To test running the DTS Insert process once again to check if I see the same error msg... I have a problem. I cannot run the INSERT process until 8pm as the client does not want us to use the source database which is DB2.

Thank you once again,
Suresh

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2008-07-16 : 15:12:59

Could be that there are transient problems with the IO subsystem - or the corrupt data is being deallocated by a cleanup section of the DTS package - and hence CHECKDB won't check it.

Look in the Windows event log and the SQL error logs to see if there are any issues with the drives hosting the temporary databases too.

Paul Randal
SQL Server MVP, Managing Director, SQLskills.com

aashirwad
Starting Member

9 Posts

Posted - 2008-07-16 : 16:26:01

Paul,

I am not sure whether these messages from SQL Server Log will help us. These messages are from a single process id and has occurred in the following order:

Log Message 1:
ex_raise2: Exception raised, major=79, minor=87, severity=22, attempting to create symptom dump

Log Message 2:
Using 'dbghelp.dll' version '4.0.5'
*Dump thread - spid = 58, PSS = 0x6388b200, EC = 0x6388b530
*
* User initiated stack dump. This is not a server exception dump.
*
*Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL\log\SQLDump0052.txt
* *******************************************************************************
*
* BEGIN STACK DUMP:
* 07/10/08 10:53:06 spid 58
*
* ex_raise2: Exception raised, major=79, minor=87, severity=22
*
* TDS Input Buffer 4096 bytes -
* insert bulk [_esyncAllied].[dbo].[MNTHDET]("DTCUST" decimal(6,0),"DTINV#
* " decimal(6,0),"DTSLSM" decimal(3,0),"DTSEQ#" decimal(2,0),"DTTYPE" deci
* mal(1,0),"DTLOCB" decimal(2,0),"DTRIPA" decimal(7,2),"DTRIPR" varchar(1)
* COLLATE SQL_Latin1_General_CP1_CI_AS,"DTDSDC" decimal(3,0),"DTDSAM" dec
* imal(7,2),"DTDATE" decimal(6,0),"DTCSBT" varchar(1) COLLATE SQL_Latin1_G
* eneral_CP1_CI_AS,"DTQTY" decimal(3,0),"DTAMT" decimal(9,2),"DTTAX" decim
* al(7,2),"DTCOMM" decimal(7,2),"DTGAL" decimal(9,4),"DTGALT" varchar(1) C
* OLLATE SQL_Latin1_General_CP1_CI_AS,"DTPACK" decimal(3,0),"DTSUPP" decim
* al(3,0),"DTSITM" decimal(3,0),"DTAPFL" decimal(1,0),"DTWHOL" decimal(1,0
* ),"DTGMFL" decimal(1,0),"DTTXEM" decimal(1,0),"DTTERM" decimal(1,0),"DTR
* OUT" varchar(2) COLLATE SQL_Latin1_General_CP1_CI_AS,"DTODDB" decimal(1,
* 0),"DTGIFT" decimal(1,0),"DTIMPW" decimal(1,0),"DTCMFL" decimal(1,0),"DT
* POST" decimal(5,2),"DTPCKF" decimal(1,0),"DTFET" decimal(7,2),"DTCRCD" v
* archar(2) COLLATE SQL_Latin1_General_CP1_CI_AS,"DTCOOP" decimal(3,0),"DT
* HOUS" decimal(2,0),"DTCRED" decimal(6,0),"DTCRDT" decimal(1,0),"DTORIG"
* decimal(6,0),"DTSLOC" decimal(2,0),"DTCIGR" decimal(1,0),"DTCSBD" decima
* l(5,0),"DTPCKP" varchar(1) COLLATE SQL_Latin1_General_CP1_CI_AS,"DTPAPO"
* decimal(9,0),"DTUNIT" decimal(7,2),"DTRETN" decimal(2,0),"DTPATY" decim
* al(1,0),"DTCENT" decimal(8,0),"DTBLHD" varchar(1) COLLATE SQL_Latin1_Gen
* eral_CP1_CI_AS,"DTPPAY" varchar(1) COLLATE SQL_Latin1_General_CP1_CI_AS,
* "DTPROD" decimal(7,0),"DTHALF" varchar(1) COLLATE SQL_Latin1_General_CP1
* _CI_AS,"DTDESC" varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS,"DTSIZE
* " varchar(5) COLLATE SQL_Latin1_General_CP1_CI_AS,"DTJKEG" varchar(1) CO
* LLATE SQL_Latin1_General_CP1_CI_AS,"DTWSHP" decimal(2,0),"DTRECV" varcha
* r(1) COLLATE SQL_Latin1_General_CP1_CI_AS,"DTDLEV" decimal(1,0),"DTDALO"
* decimal(7,2),"DTPSUP" decimal(3,0),"DTSBBA" decimal(7,2),"DTBLK" varcha
* r(5) COLLATE SQL_Latin1_General_CP1_CI_AS,"DTSBBP" decimal(3,2),"DTRLV1"
* decimal(2,0),"DTRLV2" decimal(2,0),"DTRLV3" decimal(2,0),"DTRLV4" decim
* al(2,0),"DTRLV5" decimal(2,0
*
*
* MODULE BASE END SIZE
* sqlservr 00400000 00CBAFFF 008bb000
* ntdll 7C800000 7C8BFFFF 000c0000
* kernel32 77E40000 77F41FFF 00102000
* ADVAPI32 77F50000 77FEBFFF 0009c000
* RPCRT4 77C50000 77CEEFFF 0009f000
* MSVCP71 7C3A0000 7C41AFFF 0007b000
* MSVCR71 7C340000 7C395FFF 00056000
* opends60 41060000 41065FFF 00006000
* SHELL32 7C8D0000 7D0D3FFF 00804000
* msvcrt 77BA0000 77BF9FFF 0005a000
* GDI32 77C00000 77C48FFF 00049000
* USER32 77380000 77411FFF 00092000
* SHLWAPI 77DA0000 77DF1FFF 00052000
* sqlsort 42AE0000 42B6FFFF 00090000
* ums 41070000 4107DFFF 0000e000
* comctl32 77420000 77522FFF 00103000
* sqlevn70 41080000 4108AFFF 0000b000
* NETAPI32 02C90000 02CE7FFF 00058000
* AUTHZ 02CF0000 02D03FFF 00014000
* COMRES 02EA0000 02F65FFF 000c6000
* ole32 02F70000 030A3FFF 00134000
* XOLEHLP 030B0000 030B5FFF 00006000
* MSDTCPRX 030C0000 03137FFF 00078000
* msvcp60 03140000 031A0FFF 00061000
* MTXCLU 031B0000 031C8FFF 00019000
* VERSION 031D0000 031D7FFF 00008000
* WSOCK32 031E0000 031E8FFF 00009000
* WS2_32 031F0000 03206FFF 00017000
* WS2HELP 03210000 03217FFF 00008000
* OLEAUT32 03220000 032ABFFF 0008c000
* CLUSAPI 032F0000 03301FFF 00012000
* RESUTILS 03310000 03322FFF 00013000
* USERENV 03330000 033F3FFF 000c4000
* secur32 03580000 03592FFF 00013000
* mswsock 035B0000 035F0FFF 00041000
* DNSAPI 03600000 03628FFF 00029000
* winrnr 03670000 03676FFF 00007000
* WLDAP32 03680000 036ADFFF 0002e000
* rasadhlp 036D0000 036D4FFF 00005000
* SSNETLIB 00E50000 00E65FFF 00016000
* NTMARTA 03EA0000 03EC1FFF 00022000
* SAMLIB 00EB0000 00EBEFFF 0000f000
* security 041C0000 041C3FFF 00004000
* hnetcfg 041D0000 04228FFF 00059000
* wshtcpip 04270000 04277FFF 00008000
* SSmsLPCn 04300000 04307FFF 00008000
* SSnmPN70 04310000 04316FFF 00007000
* ntdsapi 043A0000 043B4FFF 00015000
* SQLFTQRY 00EE0000 00F05FFF 00026000
* xpsp2res 10000000 102C4FFF 002c5000
* CLBCatQ 047C0000 04842FFF 00083000
* sqloledb 04850000 048D0FFF 00081000
* MSDART 00F20000 00F39FFF 0001a000
* MSDATL3 048E0000 048F4FFF 00015000
* oledb32 04C90000 04D08FFF 00079000
* OLEDB32R 04D10000 04D20FFF 00011000
* msv1_0 04DB0000 04DD6FFF 00027000
* iphlpapi 04DE0000 04DF9FFF 0001a000
* PSAPI 04E00000 04E0AFFF 0000b000
* xpsqlbot 04A30000 04A35FFF 00006000
* rsaenh 04F40000 04F6EFFF 0002f000
* xpstar 04FF0000 0503CFFF 0004d000
* SQLRESLD 05040000 0504BFFF 0000c000
* SQLSVC 05050000 0506AFFF 0001b000
* ODBC32 05070000 050ACFFF 0003d000
* COMCTL32 050B0000 05146FFF 00097000
* comdlg32 05150000 05199FFF 0004a000
* odbcbcp 051A0000 051A5FFF 00006000
* W95SCM 051B0000 051BCFFF 0000d000
* SQLUNIRL 051C0000 051ECFFF 0002d000
* WINSPOOL 051F0000 05216FFF 00027000
* SHFOLDER 05220000 05228FFF 00009000
* odbcint 05380000 05396FFF 00017000
* NDDEAPI 054B0000 054B6FFF 00007000
* SQLSVC 054C0000 054C5FFF 00006000
* xpstar 054D0000 054D8FFF 00009000
* xplog70 03560000 0356EFFF 0000f000
* xplog70 03570000 03574FFF 00005000
* dbghelp 05AF0000 05BEFFFF 00100000
*
* Edi: 6388B530: 6388B200 638C58C0 638C5230 6388B53C 6388B53C 63846038
* Esi: 6388B200: 0000003A 00000000 00B1DE22 00009AD5 00000000 00000000
* Eax: 00000000:
* Ebx: 04E9E330: 00126788 00610072 00730069 00320065 0020003A 00780045
* Ecx: 04E9E390: 00000000 0001003F 00000000 00000000 00000000 00000000
* Edx: 00103AB8: 00000000 6388B200 6388B530 00000000 00030009 000C013F
* Eip: 009BA08C: 26EB0088 90909090 EC458B90 83E47D8B B3B904C7 8B000000
* Ebp: 04E9E314: 04E9EB54 009BA9B5 6388B200 04E9EFE0 0000004F 00000057
* SegCs: 0000001B:
* EFlags: 00000246:
* Esp: 04E9E2EC: 6388B530 6388B200 04E9E330 04E9E390 04E9E2EC 04E9DF18
* SegSs: 00000023:
* *******************************************************************************
* -------------------------------------------------------------------------------
* Short Stack Dump
* 009BA08C Module(sqlservr+005BA08C) (GetOSErrString+00004F68)
* 009BA9B5 Module(sqlservr+005BA9B5) (GetOSErrString+00005891)
* 006EE757 Module(sqlservr+002EE757) (SQLExit+00186C60)
* 005FA686 Module(sqlservr+001FA686) (SQLExit+00092B8F)
* 0061F3A4 Module(sqlservr+0021F3A4) (SQLExit+000B78AD)
* 00407650 Module(sqlservr+00007650)
* 0099687B Module(sqlservr+0059687B) (CDStream::operator=+0004C6DE)
* 0099744B Module(sqlservr+0059744B) (CDStream::operator=+0004D2AE)
* 009979C2 Module(sqlservr+005979C2) (CDStream::operator=+0004D825)
* 005B90E7 Module(sqlservr+001B90E7) (SQLExit+000515F0)
* 0042B5C2 Module(sqlservr+0002B5C2)
* 005B9005 Module(sqlservr+001B9005) (SQLExit+0005150E)
* 005B903F Module(sqlservr+001B903F) (SQLExit+00051548)
* 0044BAB0 Module(sqlservr+0004BAB0)
* 005B820A Module(sqlservr+001B820A) (SQLExit+00050713)
* 005B8266 Module(sqlservr+001B8266) (SQLExit+0005076F)
* 0041D396 Module(sqlservr+0001D396)
* 0048FFA4 Module(sqlservr+0008FFA4)
* 004194B9 Module(sqlservr+000194B9)
* 004193E4 Module(sqlservr+000193E4)
* 004168CF Module(sqlservr+000168CF)
* 00787F65 Module(sqlservr+00387F65) (SQLExit+0022046E)
* 00415D04 Module(sqlservr+00015D04)
* 00416214 Module(sqlservr+00016214)
* 00415F28 Module(sqlservr+00015F28)
* 0049C32E Module(sqlservr+0009C32E)
* 0049C46A Module(sqlservr+0009C46A)
* 41075309 Module(ums+00005309) (ProcessWorkRequests+000002D9 Line 456+00000000)
* 41074978 Module(ums+00004978) (ThreadStartRoutine+00000098 Line 263+00000007)
* 7C34940F Module(MSVCR71+0000940F) (endthread+000000AA)
* 77E6608B Module(kernel32+0002608B) (GetModuleFileNameA+000000EB)
* -------------------------------------------------------------------------------

Log Message 3:
Stack Signature for the dump is 0x3C5222AE

Log Message 4:
Error: 7987, Severity: 22, State: 1

Log Message 5:
A possible database consistency problem has been detected on database '_esyncAllied'. DBCC CHECKDB and DBCC CHECKCATALOG should be run on database '_esyncAllied'..

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2008-07-16 : 16:34:19

No - this the stack dump from SQL Server when it discovers the corruption.

One more thing to try - run memory diagnostics on the box - might not be the IO subsystem - could be bad memory.

Paul Randal
SQL Server MVP, Managing Director, SQLskills.com

aashirwad
Starting Member

9 Posts

Posted - 2008-07-16 : 16:42:15

Run memory diagnostics... can you tell me how to do it?

Thanks,
Suresh

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2008-07-16 : 16:58:48

Use whatever your hardware vendor provides or see http://oca.microsoft.com/en/windiag.asp

Paul Randal
SQL Server MVP, Managing Director, SQLskills.com

aashirwad
Starting Member

9 Posts

Posted - 2008-07-25 : 11:17:45

Paul,
Just to close this issue... I solved the database consistency problem by recreating the indexes on my database and it worked fine.

Thanks for your valuable support.

Suresh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources