Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

dbcc allow data lose problem with increasing log Expand / Collapse
Author
Message
Posted Monday, October 27, 2008 12:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, October 27, 2008 12:44 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 2:09 PM
Points: 2,035, Visits: 1,655
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
Posted Monday, October 27, 2008 1:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, October 27, 2008 2:19 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 11:47 AM
Points: 41,525, Visits: 34,442
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
Posted Monday, October 27, 2008 7:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, October 27, 2008 7:49 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 2:09 PM
Points: 2,035, Visits: 1,655
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
Posted Tuesday, October 28, 2008 2:22 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 11:47 AM
Points: 41,525, Visits: 34,442
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 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
Posted Tuesday, October 28, 2008 7:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, October 28, 2008 9:04 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 2:09 PM
Points: 2,035, Visits: 1,655
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
Posted Tuesday, October 28, 2008 11:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase 12»»

Permissions Expand / Collapse