August 26, 2008 at 2:41 pm
I have been encountering the following error messages when I execute DBCC Checkdb against some of the SQL 2000 databases in our environment.
DBCC results for 'erReportesEUFES61'.
Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1234103437, index ID 1, partition ID 362353179557888, alloc unit ID 71856997264064512 (type LOB data). The off-row data node at page (1:1642), slot 4, text ID 21783052288 is not referenced.
There are 89 rows in 5 pages for object "erReportesEUFES61".
CHECKDB found 0 allocation errors and 1 consistency errors in table 'erReportesEUFES61' (object ID 1234103437).
I have been looking at the issue on a test server and found that I am able to resolve the error by executing DBCC CheckDB with the repair_allow_data_loss option set. I captured the rows for the table before and after fixing the problem and see no difference. Is this the only area where I would need to verify that no data loss occurred?
The consistency error indicates that there is a text object that is not referenced. Does this mean a row may have been deleted, but the text object it referenced was not deleted at the same time?
August 26, 2008 at 2:54 pm
SQL 2000 or SQL 2005? The checkDB output looks like SQL 2005.
Do you have a clean backup?
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
August 26, 2008 at 3:29 pm
Unfortunately we do not have a backup from before the error message as it has been around for some time now. I am concerned that the application that uses the database is causing the issue, so I am keeping a record of the errors now and watching for new occurences of the errors. Then I will have a clean backup for the new error.
August 26, 2008 at 3:53 pm
It's definitely a 2005 database, I changed that message for 2005 when I rewrote CHECKDB. Yes, REPAIR_ALLOW_DATA_LOSS will do nothing in this case except remove the orphaned LOB node.
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
August 27, 2008 at 6:50 am
Yes the database was recently upgraded to SQL2005. The consistency errors were identified though in the SQL2000 backup copy we have from the upgrade activities.
I am thinking this is an application issue unless there is a scenario in SQL 2000 and/or 2005 that would orphan LOB data.
Thanks for the information on the nature of the error message
August 27, 2008 at 9:35 am
Applications cannot cause this type of corruption - it's either a SQL Server bug, a corruption caused by the IO subsystem, or a corruption that existed in 2000 but wasn't spotted until the database was upgraded.
You should be able to run repair and have it take care of the problem.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply