Finding a possible corrupt bit

  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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