November 14, 2012 at 12:09 pm
To All I have a table of the following definition
USE [CISPROD_DEV]
GO
/****** Object: Table [ADVANCED].[BIF041] Script Date: 11/14/2012 13:00:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [ADVANCED].[BIF041](
[C_CUSTOMER] [char](15) NOT NULL,
[C_ACCOUNT] [char](15) NOT NULL,
[T_TRANSDT] [datetime] NOT NULL,
[C_TRANSCODE] [char](4) NOT NULL,
[Y_AMOUNT] [decimal](15, 2) NOT NULL,
[I_BILLNUMBER] [decimal](15, 0) NOT NULL,
[I_TRANSNUM] [decimal](15, 0) NOT NULL,
[C_USERID] [char](15) NOT NULL,
[N_SUMFLAG] [decimal](1, 0) NOT NULL,
[C_ARCODE] [char](2) NOT NULL,
[T_DATETIME] [datetime] NOT NULL,
[I_TOTAL] [int] NOT NULL,
[C_COMPANY] [char](2) NOT NULL,
[C_DIVISION] [char](2) NOT NULL,
[I_SONUM] [numeric](15, 0) NOT NULL,
[I_BIF041PK] [decimal](15, 0) NOT NULL,
[D_AGING] [datetime] NULL,
[L_EXCLUDETRANS] [bit] NOT NULL,
CONSTRAINT [BIF041_I_BIF041PK] PRIMARY KEY CLUSTERED
(
[I_BIF041PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [CISDATA]
) ON [CISDATA]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [ADVANCED].[BIF041] ADD DEFAULT ('') FOR [C_CUSTOMER]
GO
ALTER TABLE [ADVANCED].[BIF041] ADD DEFAULT ('') FOR [C_ACCOUNT]
GO
ALTER TABLE [ADVANCED].[BIF041] ADD CONSTRAINT [BIF041_T_TRANSDT_ZDEFA] DEFAULT (getdate()) FOR [T_TRANSDT]
GO
ALTER TABLE [ADVANCED].[BIF041] ADD DEFAULT ('') FOR [C_TRANSCODE]
GO
ALTER TABLE [ADVANCED].[BIF041] ADD DEFAULT ((0)) FOR [Y_AMOUNT]
GO
ALTER TABLE [ADVANCED].[BIF041] ADD DEFAULT ((0)) FOR [I_BILLNUMBER]
GO
ALTER TABLE [ADVANCED].[BIF041] ADD DEFAULT ((0)) FOR [I_TRANSNUM]
GO
ALTER TABLE [ADVANCED].[BIF041] ADD DEFAULT ('') FOR [C_USERID]
GO
ALTER TABLE [ADVANCED].[BIF041] ADD DEFAULT ((0)) FOR [N_SUMFLAG]
GO
ALTER TABLE [ADVANCED].[BIF041] ADD DEFAULT ('') FOR [C_ARCODE]
GO
ALTER TABLE [ADVANCED].[BIF041] ADD DEFAULT (getdate()) FOR [T_DATETIME]
GO
ALTER TABLE [ADVANCED].[BIF041] ADD DEFAULT ((0)) FOR [I_TOTAL]
GO
ALTER TABLE [ADVANCED].[BIF041] ADD DEFAULT ('01') FOR [C_COMPANY]
GO
ALTER TABLE [ADVANCED].[BIF041] ADD DEFAULT ('01') FOR [C_DIVISION]
GO
ALTER TABLE [ADVANCED].[BIF041] ADD DEFAULT ((0)) FOR [I_SONUM]
GO
ALTER TABLE [ADVANCED].[BIF041] ADD CONSTRAINT [BIF041_I_BIF041PK_ZDEFA] DEFAULT ((0)) FOR [I_BIF041PK]
GO
ALTER TABLE [ADVANCED].[BIF041] ADD DEFAULT ((0)) FOR [L_EXCLUDETRANS]
GO
When the weekly maintenance ran the Update statistics died on this table saying there was a null value in a non null field. So I ran DBCC Checktable against the table and discovered the same type of error. I am hesitant to loose data and since the minamum repair level recommended by DBCC was to allow data loss I decided I would like to find the offending record. So after some digging I discovered the following script returned some odd results.
select L_EXCLUDETRANS, count(*) from ADVANCED.BIF041 group by L_EXCLUDETRANS
this returned
L_EXCLUDETRANS Count
0 107,242,316
0 1
1 747,242
So the question became why is it picking up a second 0 as a unique value. My second problem became how to find the record with the bad 0. I have tried selecting by zero selcting by 1 selecting by not in 0 or 1; not equal to 0; not equal to 1 I have tried restoring the DB to a different environment and the problem follows. I have imported the table alone into a new DB and the problem does not follow. I have tried to use Data compare in visual studios but it states all records match between the restored DB and the DB with the copied table. Does anyone have any tricks or tips to find this record?
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
November 14, 2012 at 12:58 pm
Try checkDB with data purity. See if it gives you any more useful errors
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 14, 2012 at 1:11 pm
I tried that and got the same error as before.
row ID 21. Column 'L_EXCLUDETRANS' was created NOT NULL, but is NULL in the row
I have looked at ROW 21 extensively even removing it in my TEST server were I did the restore and still get the error so I am assuming that does not actually point to record 21
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
November 14, 2012 at 1:52 pm
Post the errors? I can't see your error log from here.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 14, 2012 at 2:02 pm
Query Result
DBCC results for 'ADVANCED.BIF041'.
Msg 8970, Level 16, State 1, Line 1
Row error: Object ID 292196091, index ID 1, partition ID 72057617438736384, alloc unit ID 72057617450598400 (type In-row data), page ID (7:4432313), row ID 21. Column 'L_EXCLUDETRANS' was created NOT NULL, but is NULL in the row.
There are 108182060 rows in 1967047 pages for object "ADVANCED.BIF041".
CHECKTABLE found 0 allocation errors and 1 consistency errors in table 'ADVANCED.BIF041' (object ID 292196091).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (CISPROD.ADVANCED.BIF041).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Errorlog
2012-11-14 14:07:41.74 spid55 DBCC CHECKTABLE (CISPROD.ADVANCED.BIF041) WITH data_purity executed by INTRANET\DHumphries found 1 errors and repaired 0 errors. Elapsed time: 0 hours 4 minutes 25 seconds.
2012-11-14 14:07:41.74 spid55 Using 'dbghelp.dll' version '4.0.5'
2012-11-14 14:07:41.74 spid55 **Dump thread - spid = 55, PSS = 0x0000000257FBBC00, EC = 0x0000000257FBBC10
2012-11-14 14:07:41.74 spid55 ***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0006.txt
2012-11-14 14:07:41.74 spid55 * *******************************************************************************
2012-11-14 14:07:41.74 spid55 *
2012-11-14 14:07:41.74 spid55 * BEGIN STACK DUMP:
2012-11-14 14:07:41.74 spid55 * 11/14/12 14:07:41 spid 55
2012-11-14 14:07:41.74 spid55 *
2012-11-14 14:07:41.74 spid55 * DBCC database corruption
2012-11-14 14:07:41.74 spid55 *
2012-11-14 14:07:41.74 spid55 * Input Buffer 136 bytes -
2012-11-14 14:07:41.74 spid55 * DBCC CHECKTABLE ("[ADVANCED].[BIF041]") WITH DATA_PURITY;
2012-11-14 14:07:41.75 spid55 *
2012-11-14 14:07:41.75 spid55 * *******************************************************************************
2012-11-14 14:07:41.75 spid55 * -------------------------------------------------------------------------------
2012-11-14 14:07:41.75 spid55 * Short Stack Dump
2012-11-14 14:07:41.77 spid55 Stack Signature for the dump is 0x0000000000000166
2012-11-14 14:07:56.68 spid55 External dump process return code 0x20000001.
External dump process returned no errors.
SQLDump0006.txt
=====================================================================
BugCheck Dump
=====================================================================
This file is generated by Microsoft SQL Server
version 9.00.4060.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 2.5.0
Current time is 14:07:41 11/14/12.
32 Unknown CPU 9., 14 Mhz processor (s).
Windows NT 6.0 Build 6002 CSD Service Pack 2.
Memory
MemoryLoad = 34%
Total Physical = 262129 MB
Available Physical = 4029 MB
Total Page File = 475924 MB
Available Page File = 217239 MB
Total Virtual = 8388607 MB
Available Virtual = 8123887 MB
DBCC RESULTS
--------------------
<DbccResults>
<Dbcc ID="0" Error="8970" Severity="16" State="1">Row error: Object ID 292196091, index ID 1, partition ID 720576
17438736384, alloc unit ID 72057617450598400 (type In-row data), page ID (7:4432313), row ID 21. Column 'L_EXCLUD
ETRANS' was created NOT NULL, but is NULL in the row.</Dbcc>
<Dbcc ID="1" Error="2593" Severity="10" State="1">There are 108182060 rows in 1967047 pages for object "ADVANCED.
BIF041".</Dbcc>
<Dbcc ID="2" Error="8990" Severity="10" State="1">CHECKTABLE found 0 allocation errors and 1 consistency errors i
n table 'ADVANCED.BIF041' (object ID 292196091).</Dbcc>
<Dbcc ID="3" Error="8957" Severity="-1" State="1">DBCC CHECKTABLE (CISPROD.ADVANCED.BIF041) WITH data_purity exec
uted by INTRANET\DHumphries found 1 errors and repaired 0 errors. Elapsed time: 0 hours 4 minutes 25 seconds.</Db
cc>
<Dbcc ID="4" Error="8958" Severity="10" State="1">repair_allow_data_loss is the minimum repair level for the erro
rs found by DBCC CHECKTABLE (CISPROD.ADVANCED.BIF041).</Dbcc>
</DbccResults>
**Dump thread - spid = 55, PSS = 0x0000000257FBBC00, EC = 0x0000000257FBBC10
***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0006.txt
* *******************************************************************************
*
* BEGIN STACK DUMP:
* 11/14/12 14:07:41 spid 55
*
* DBCC database corruption
*
* Input Buffer 136 bytes -
* DBCC CHECKTABLE ("[ADVANCED].[BIF041]") WITH DATA_PURITY;
*
* *******************************************************************************
* -------------------------------------------------------------------------------
* Short Stack Dump
PSS @0x0000000257FBBC00
-----------------------
CSession @0x0000000257FBA410
----------------------------
m_spid = 55 m_cRef = 14 m_rgcRefType[0] = 1
m_rgcRefType[1] = 1 m_rgcRefType[2] = 11 m_rgcRefType[3] = 1
m_rgcRefType[4] = 0 m_rgcRefType[5] = 0 m_pmo = 0x0000000257FBA080
m_pstackBhfPool = 0x0000000000000000 m_dwLoginFlags = 0x83e0 m_fBackground = 0
m_fClientRequestConnReset = 0 m_fUserProc = -1 m_fConnReset = 0
m_fIsConnReset = 0 m_fInLogin = 0 m_fAuditLoginSent = 1
m_fAuditLoginFailedSent = 0 m_fReplRelease = 0 m_fKill = 0
m_ulLoginStamp = 540745 m_eclClient = 5 m_protType = 5
m_hHttpToken = FFFFFFFFFFFFFFFF
m_pV7LoginRec
---------------------
0000000000000000: 64010000 03000a73 00100000 00000006 44170000 †d......s........D...
0000000000000014: 00000000 e0830000 00000000 00000000 5e000c00 †................^...
0000000000000028: 00000000 00000000 76002e00 d2001200 f6000000 †........v...........
000000000000003C: f6001c00 2e010000 2e010700 0023aead af793c01 †.............#...y<.
0000000000000050: 28006401 00006401 00000000 0000†††††††††††††††(.d...d.......
CPhysicalConnection @0x0000000257FBA2E0
---------------------------------------
m_pPhyConn->m_pmo = 0x0000000257FBA080
m_pPhyConn->m_pNetConn = 0x0000000257FBAB00
m_pPhyConn->m_pConnList = 0x0000000257FBA3E0
m_pPhyConn->m_pSess = 0x0000000257FBA410 m_pPhyConn->m_fTracked = -1
m_pPhyConn->m_cbPacketsize = 4096 m_pPhyConn->m_fMars = 0 m_pPhyConn->m_fKill = 0
CBatch @0x0000000257FBAFD0
--------------------------
m_pSess = 0x0000000257FBA410 m_pConn = 0x0000000257FBAEC0 m_cRef = 3
m_rgcRefType[0] = 1 m_rgcRefType[1] = 1 m_rgcRefType[2] = 1
m_rgcRefType[3] = 0 m_rgcRefType[4] = 0 m_pTask = 0x0000000011862328
EXCEPT (null) @0x000000005BA4A858
---------------------------------
exc_number = 0 exc_severity = 0 exc_func = 0x0000000001B03960
Task @0x0000000011862328
------------------------
CPU Ticks used (ms) = 21583 Task State = 2
WAITINFO_INTERNAL: WaitResource = 0x0000000000000000 WAITINFO_INTERNAL: WaitType = 0x0
WAITINFO_INTERNAL: WaitSpinlock = 0x0000000000000000 SchedulerId = 0x9
ThreadId = 0x240c m_state = 0 m_eAbortSev = 0
EC @0x0000000257FBBC10
----------------------
spid = 55 ecid = 0 ec_stat = 0x0
ec_stat2 = 0x0 ec_atomic = 0x0 __fSubProc = 1
ec_dbccContext = 0x000000005BA4EF20 __pSETLS = 0x0000000257FBAF30 __pSEParams = 0x0000000257FBB350
__pDbLocks = 0x0000000257FBBCD0
SEInternalTLS @0x0000000257FBAF30
---------------------------------
m_flags = 0 m_TLSstatus = 3 m_owningTask = 0x0000000011862328
m_activeHeapDatasetList = 0x0000000257FBAF30
m_activeIndexDatasetList = 0x0000000257FBAF40
SEParams @0x0000000257FBB350
----------------------------
m_lockTimeout = -1 m_isoLevel = 4096 m_logDontReplicate = 0
m_neverReplicate = 0 m_XactWorkspace = 0x000000206A6D1820
m_pSessionLocks = 0x0000000257FBBD80 m_pDbLocks = 0x0000000257FBBCD0
m_execStats = 0x0000000A739B5470 m_pAllocFileLimit = 0x0000000000000000
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
November 14, 2012 at 3:47 pm
Thank you, this is all I needed.
Row error: Object ID 292196091, index ID 1, partition ID 72057617438736384, alloc unit ID 72057617450598400 (type In-row data), page ID (7:4432313), row ID 21. Column 'L_EXCLUDETRANS' was created NOT NULL, but is NULL in the row.
Right, what's the primary key of this table?
What's the data type of L_EXCLUDETRANS?
Run the following save the results to a text file and attach that file to your post.
DBCC TRACEON (3604)
DBCC PAGE ('CISPROD',7:4432313,3 )
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 15, 2012 at 8:42 am
Gail,
Thank you for your help I actually found the row I needed to fix and was able to get this problem taken care of. It has helped me realize though that I have a great deal to learn about DBCC and need to read up some more on it.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply