SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


dbcc allow data lose problem with increasing log


dbcc allow data lose problem with increasing log

Author
Message
lyletan
lyletan
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 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
Paul Randal
Paul Randal
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8062 Visits: 1719
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
lyletan
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220839 Visits: 46279
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
lyletan
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 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
Paul Randal
Paul Randal
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8062 Visits: 1719
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220839 Visits: 46279
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
lyletan
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 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
Paul Randal
Paul Randal
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8062 Visits: 1719
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
lyletan
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search