February 11, 2014 at 5:36 am
Hi,
Database integrity job failed with below error.
In job output log ,i find below error for one of the db,
DateTime: 2014-02-07 23:43:56
Command: DBCC CHECKDB ([Dbxxxxx]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY
Msg 2508, Level 16, State 1, Server xxxxxxx, Line 1
The In-row data USED page count for object "TblRecoTemp", index ID 2, partition ID 648250279002112, alloc unit ID 648250279002112 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.
Msg 2508, Level 16, State 3, Server xxxxxxxxxxxxxxserver, Line 1
The In-row data RSVD page count for object "TblRecolTemp", index ID 2, partition ID 648250279002112, alloc unit ID 648250279002112 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'TblRecolTemp' (object ID 1301579675).
CHECKDB found 0 allocation errors and 2 consistency errors in database 'Dbxxxxxxx'.
Outcome: Failed
Duration: 00:00:02
DateTime: 2014-02-07 23:43:58
can any one help to resolve the error.
advance thanks.
February 11, 2014 at 5:49 am
It can be addressed using repair_allow_data_loss.. but you might loose some data ... Please read more on repair_allow_data_loss option that CHECKDB provides here http://technet.microsoft.com/en-us/library/ms176064.aspx
February 11, 2014 at 6:38 am
chetan.deshpande001 (2/11/2014)
It can be addressed using repair_allow_data_loss.. but you might loose some data ... Please read more on repair_allow_data_loss option that CHECKDB provides here http://technet.microsoft.com/en-us/library/ms176064.aspx
NO! NO! NO! It does NOT require repair_allow_data_loss, nor does anything in the message suggest that such a drastic option be used.
Please be careful of giving incorrect advice around database corruption questions.
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
February 11, 2014 at 6:41 am
This is a minor error, not something you particularly need to worry about. As the messages in the job output suggest, you can resolve this completely and with absolutely no risk of data loss, no downtime, no outages by just running DBCC UPDATEUSAGE. Schedule it to run next time you run index rebuilds (before the index rebuilds)
The In-row data USED page count for object "TblRecoTemp", index ID 2, partition ID 648250279002112, alloc unit ID 648250279002112 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE
In versions of SQL before SQL 2005, there were bugs around the metadata page usage counts and these page usage counts could often be wring. While most of those errors were corrected in SQL 2005 and various patches of SQL 2005, a few have persisted into later versions of SQL, resulting in the error that you got from CheckDB
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
February 11, 2014 at 7:08 am
Thanks for update,
Sql server is 2008 version,
on the Sql error log i coud see the below error to
DBCC CHECKDB (DbDtkMain) WITH all_errormsgs, no_infomsgs, data_purity executed by NT AUTHORITY\SYSTEM found 2 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 2 seconds. Internal database snapshot has
split point LSN = 00000b8f:000003a2:0001 and first LSN = 00000b8f:000003a1:0001.
February 11, 2014 at 7:27 am
My comment directly above stands.
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
February 11, 2014 at 8:26 am
Thanks for update.
Can i run DBCC updateusage only for that particular table which i got error.
please suggest for below steps which am following is correct.
dbcc updateusage(databsename,'TblRecolTemp')
use [databsename]
DBCC checktable ('TblRecolTemp'
February 11, 2014 at 8:31 am
Just run DBCC updateusage without parameters, schedule it for your next maintenance window, whenever you do index maintenance.
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
February 11, 2014 at 8:47 am
Maybe this will ease your mind. I had the same issue two weeks ago and did the same thing that Gail is advising. The results were three tables were updated and the output looked like this:
DBCC UPDATEUSAGE: counts updated for table '<your table name>' (index '<your table name/index name>', partition 1):
USED pages (In-row Data): changed from (5) to (14) pages.
RSVD pages (In-row Data): changed from (-25) to (14) pages.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
A follow up DBCC CheckDB returned no errors.
I know the first sight of an error from DBCC CheckDB can make your heart sink; but, in your case you should be in good shape.
February 11, 2014 at 9:01 am
chetan.deshpande001 (2/11/2014)
It can be addressed using repair_allow_data_loss.. but you might loose some data ... Please read more on repair_allow_data_loss option that CHECKDB provides here http://technet.microsoft.com/en-us/library/ms176064.aspx
You do know that "some" in this case can include up to the entire database. Every other action possible is preferable to running repair_allow_data_loss. It should only be a last resort after all possibilities of any other type of recovery have failed because it is so catastrophic in potential.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply