July 30, 2013 at 6:36 am
hello there,
I have an issue with CheckDB giving the error below.
If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000]
checking xxxx [SQLSTATE 01000]
Msg 8914, Sev 16, State 1, Line 1 : Incorrect PFS free space information for page (1:208) in object ID 60, index ID 1, partition ID 281474980642816,
Is there anything i can do about that i have read that this is a bug..
Thank you in advance
July 30, 2013 at 6:51 am
Please run the following and post the full, complete and unedited output.
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
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
July 30, 2013 at 6:57 am
There you go thank you in advance
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:208) in object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB data). Expected value 0_PCT_FULL, actual value 100_PCT_FULL.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'sys.sysobjvalues' (object ID 60).
CHECKDB found 0 allocation errors and 1 consistency errors in database 'XXX'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (XXX).
July 30, 2013 at 6:58 am
I usually have a good enough backup where I can restore a db in its entirety (or to a staging database where I can just pull out the info I need and reinsert it as needed), but...
In the cases that I don't, try a couple of things. They're quick and dirty, don't always work, and won't give you any insight into WHY the corruption happened, but it might help get you past the issue.
1) I write a script to create a new table with the same schema as the "broken" one, and then I'll do an insert into it with the source being the "broken" table. Then rename the tables such that the new table has the same name as the "broken" one.
2) If that doesn't work, then I'll fire up a new SSIS package that basically pulls the data out of the "broken" table, puts it through a few bogus transformations that ends up with the same data types For example I'd take all of my strings and take them from varchar(20) to varchar(50) and then back to varchar(20)...maybe take the ints to numeric and back, etc. And then truncate the "broken" table and replace it with the laundered data.
...Or a combination of the two. The general idea of these tactics is to force whatever data/structure is broken into a state where it has to be re-processed and re-written to the table. This doesn't always work, but it's a quick enough process that it might be worth your time to try.
-G
July 30, 2013 at 7:15 am
have ran the
DBCC CHECKDB (xxx, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
Msg 8914, Level 16, State 1, Line 2
Incorrect PFS free space information for page (1:208) in object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB data). Expected value 0_PCT_FULL, actual value 100_PCT_FULL.
The error has been repaired.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'sys.sysobjvalues' (object ID 60).
CHECKDB fixed 0 allocation errors and 1 consistency errors in table 'sys.sysobjvalues' (object ID 60).
CHECKDB found 0 allocation errors and 1 consistency errors in database 'zzz'.
CHECKDB fixed 0 allocation errors and 1 consistency errors in database 'zzz'.
July 30, 2013 at 7:42 am
According to the output, it looks like it fixed it. Have you run it multiple times and continue to get the notification that it is fixing things?
-G
July 30, 2013 at 7:46 am
Yeah i m thank you for your help
July 30, 2013 at 9:20 am
The correct solution here is to run checkDB with repair allow data loss. It won't actually lose any data, it's just that the particular fix requires that option.
It is discussed here: http://www.sqlservercentral.com/articles/65804/
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
July 30, 2013 at 9:39 am
Greg A Goss (7/30/2013)
1) I write a script to create a new table with the same schema as the "broken" one, and then I'll do an insert into it with the source being the "broken" table. Then rename the tables such that the new table has the same name as the "broken" one.
Not possible in this case as the table involved was a system table
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
July 30, 2013 at 10:33 am
GilaMonster (7/30/2013)
Greg A Goss (7/30/2013)
1) I write a script to create a new table with the same schema as the "broken" one, and then I'll do an insert into it with the source being the "broken" table. Then rename the tables such that the new table has the same name as the "broken" one.Not possible in this case as the table involved was a system table
Very true, but that was suggested before we were explicitly told it was a sys table and I didn't pay attention to the object id. That'll teach me! 😉
-G
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply