dbcc allow data lose problem with increasing log

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Data Corruption » dbcc allow data lose problem with increasing...

11 posts, Page 1 of 2

1 2 »»

dbcc allow data lose problem with increasing log

Rate Topic

Display Mode

Topic Options

Author

Message

lyletan

Posted Monday, October 27, 2008 12:09 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, October 12, 2010 1:57 AM
Points: 8, Visits: 69

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

Post #591921

Paul Randal

Posted Monday, October 27, 2008 12:44 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:23 PM
Points: 1,909, Visits: 1,601

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

Post #591927

lyletan

Posted Monday, October 27, 2008 1:34 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, October 12, 2010 1:57 AM
Points: 8, Visits: 69

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

Post #591946

GilaMonster

Posted Monday, October 27, 2008 2:19 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 37,741, Visits: 30,019

What were the full results of the CheckDB that listed the errors?

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #591955

lyletan

Posted Monday, October 27, 2008 7:15 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, October 12, 2010 1:57 AM
Points: 8, Visits: 69

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

Post #592564

Paul Randal

Posted Monday, October 27, 2008 7:49 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:23 PM
Points: 1,909, Visits: 1,601

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

Post #592574

GilaMonster

Posted Tuesday, October 28, 2008 2:22 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 37,741, Visits: 30,019

lyletan (10/27/2008)

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 2008, MVP
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

Post #592664

lyletan

Posted Tuesday, October 28, 2008 7:19 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, October 12, 2010 1:57 AM
Points: 8, Visits: 69

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

Post #593325

Paul Randal

Posted Tuesday, October 28, 2008 9:04 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:23 PM
Points: 1,909, Visits: 1,601

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

Post #593347

lyletan

Posted Tuesday, October 28, 2008 11:23 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, October 12, 2010 1:57 AM
Points: 8, Visits: 69

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

Post #593390

« Prev Topic | Next Topic »

11 posts, Page 1 of 2

1 2 »»

Permissions