dbcc allow data lose problem with increasing log

  • Hi Everyone

    I do have server problem when i make a dbcc checkdb allow data lose script. the log of the database was increasing currently 114gig it started with I think 5MB log, then it has

    server error message “could not continue scan with no lock due to data movement”

    server message 601.

    Now its been 3days running, Im running out of space..your help would very much appreciated..thanks

    Regards,

    Vincent

  • Hi Vincent,

    Sounds like its rebuilding a bunch of indexes after removing some data pages. Btw - everything that repair does is fully logged, regardless of your recovery model.

    What were the errors you're trying to fix with repair? Don't you have backups to restore from instead? How big is the database?

    Thanks

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • HI Paul

    Many thanks for the reply I do appreciate it.

    1 What were the errors you're trying to fix with repair?

    2 Don't you have backups to restore from instead?

    3 How big is the database?

    1. repair corrupted tables and indexes

    2. Actually this is my last backup

    3. 4Gig

    If you have any dbcc script or defrag can I have it..thanks

    Regards

    Vincent

  • What were the full results of the CheckDB that listed the 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
  • Hi Gail

    The message is:

    Could not continue scan with NOLOCK due to data movement.

    Server: Msg 601, Level 12, State 1, Line 1

    Could not continue scan with NOLOCK due to data movement.

    Server: Msg 601, Level 12, State 1, Line 1

    Could not continue scan with NOLOCK due to data movement.

    Server: Msg 601, Level 12, State 1, Line 1

    Could not continue scan with NOLOCK due to data movement.

    Server: Msg 601, Level 12, State 1, Line 1

    Thanks

    Vincent

  • Can you post the results of the following command please:

    DBCC CHECKDB (yourdbname) WITH ALL_ERRORMSGS, NO_INFOMSGS

    It shouldn't take 3 days to run on a 4-GB database unless there's something very badly wrong with you I/O subsystem.

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • lyletan (10/27/2008)


    Hi Gail

    The message is:

    Could not continue scan with NOLOCK due to data movement.

    Server: Msg 601, Level 12, State 1, Line 1

    Could not continue scan with NOLOCK due to data movement.

    Server: Msg 601, Level 12, State 1, Line 1

    Could not continue scan with NOLOCK due to data movement.

    Server: Msg 601, Level 12, State 1, Line 1

    Could not continue scan with NOLOCK due to data movement.

    Server: Msg 601, Level 12, State 1, Line 1

    Thanks

    Vincent

    I meant the corruption errors that made you decide to run checkDB in the first place. Same command as Paul's asking for.

    Will be more specific next time.

    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
  • Hi Paul

    Theirs the message when I run the script.

    Server: Msg 8951, Level 16, State 1, Line 1

    Table error: Table 'cicmpy' (ID 105767434). Missing or invalid key in index 'ix_debcode' (ID 35) for the row:

    Server: Msg 8955, Level 16, State 1, Line 1

    Data row (1:17392:1) identified by (RID = (1:17392:1) cmp_wwn = 5A3C5B0F-D651-4098-B2A3-86BF317C2309È™Ú) has index values (debcode = ' 2008088300354' and debnr = ' 2859' and cmp_wwn = 5A3C5B0F-D651-4098-B2A3-86BF317C2309P¢Ú).

    Server: Msg 8951, Level 16, State 1, Line 1

    Table error: Table 'cicmpy' (ID 105767434). Missing or invalid key in index 'ix_timestamp' (ID 37) for the row:

    Server: Msg 8955, Level 16, State 1, Line 1

    Data row (1:17392:1) identified by (RID = (1:17392:1) cmp_wwn = 5A3C5B0F-D651-4098-B2A3-86BF317C2309È™Ú) has index values (timestamp = and Administration = '883' and cmp_type = 'C' and cmp_wwn = 5A3C5B0F-D651-4098-B2A3-86BF317C2309P¢Ú).

    Server: Msg 8929, Level 16, State 1, Line 1

    Object ID 948198428: Errors found in text ID 188394831872 owned by data record identified by RID = (1:124888:8) ID = F9479DFA-F531-4ED4-8BBF-CABF88860B26üÕÚ.

    Server: Msg 8929, Level 16, State 1, Line 1

    Object ID 948198428: Errors found in text ID 185351864320 owned by data record identified by RID = (1:169747:2) ID = E8C94033-ADAF-4D3F-A0FE-5ED688476E68üÕÚ.

    Server: Msg 8961, Level 16, State 1, Line 1

    Table error: Object ID 948198428. The text, ntext, or image node at page (1:56400), slot 3, text ID 188394831872 does not match its reference from page (1:169747), slot 2.

    Server: Msg 8974, Level 16, State 1, Line 1

    Text node referenced by more than one node. Object ID 948198428, text, ntext, or image node page (1:56400), slot 3, text ID 188394831872 is pointed to by page (1:169747), slot 2 and by page (1:124888), slot 8.

    Server: Msg 8964, Level 16, State 1, Line 1

    Table error: Object ID 948198428. The text, ntext, or image node at page (1:436570), slot 1, text ID 185351864320 is not referenced.

    CHECKDB found 0 allocation errors and 2 consistency errors in table 'cicmpy' (object ID 105767434).

    CHECKDB found 0 allocation errors and 5 consistency errors in table 'BacoDiscussions' (object ID 948198428).

    Server: Msg 8935, Level 16, State 1, Line 1

    Table error: Object ID 1339151816, index ID 1. The previous link (1:168520) on page (1:171608) does not match the previous page (1:436528) that the parent (1:6401), slot 20 expects for this page.

    Server: Msg 8936, Level 16, State 1, Line 1

    Table error: Object ID 1339151816, index ID 1. B-tree chain linkage mismatch. (1:436528)->next = (1:171608), but (1:171608)->Prev = (1:168520).

    Server: Msg 8934, Level 16, State 1, Line 1

    Table error: Object ID 1339151816, index ID 1. The high key value on page (1:436528) (level 0) is not less than the low key value in the parent (0:1), slot 0 of the next page (1:171608).

    Server: Msg 8934, Level 16, State 1, Line 1

    Table error: Object ID 1339151816, index ID 2. The high key value on page (1:6927) (level 0) is not less than the low key value in the parent (0:1), slot 0 of the next page (1:436536).

    Server: Msg 8935, Level 16, State 1, Line 1

    Table error: Object ID 1339151816, index ID 2. The previous link (1:436536) on page (1:8199) does not match the previous page (1:6927) that the parent (1:4810), slot 119 expects for this page.

    Server: Msg 8977, Level 16, State 1, Line 1

    Table error: Object ID 1339151816, index ID 2. Parent node for page (1:436536) was not encountered.

    CHECKDB found 0 allocation errors and 6 consistency errors in table 'Addresses' (object ID 1339151816).

    CHECKDB found 0 allocation errors and 13 consistency errors in database '883'.

    repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (883 ).

    Thanks

    Vincent

  • These errors shouldn't take that long to repair on a small database like yours. To confirm, you're saying that CHECKDB has been running for 3 days without completing?

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • Hi Paul

    Thanks for the reply, yes 3 straight days and Im out of space, I wonder why also why it take that long.

    btw this is my script DBCC CHECKDB ([883],REPAIR_ALLOW_DATA_LOSS)

    Regards

    Vincent

  • Is there any activity in the database? I mean is CHECKDB actually doing anything?

    Check whether any I/Os are happening. Also, look at the percent_complete column of sys.dm_exec_requests for the DBCC command so see if any progress is being made.

    If you're running on SP2, can you check whether a 5268 error message has been output to the errorlog?

    Thanks

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply