dbcc allow data lose problem with increasing log

Question

dbcc allow data lose problem with increasing log

lyletan

SSC Veteran

Points: 236
More actions
October 27, 2008 at 12:09 am

#200363

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

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

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

Paul Randal One Orange Chip Points: 29544 More actions · Answer 1

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

lyletan SSC Veteran Points: 236 More actions · Answer 2

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

lyletan SSC Veteran Points: 236 More actions · Answer 4

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

Paul Randal One Orange Chip Points: 29544 More actions · Answer 5

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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

lyletan SSC Veteran Points: 236 More actions · Answer 7

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

Paul Randal One Orange Chip Points: 29544 More actions · Answer 8

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

lyletan SSC Veteran Points: 236 More actions · Answer 9

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

Paul Randal One Orange Chip Points: 29544 More actions · Answer 10

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