Hi All,
I have recently resolved an issue which I will describe below which has led me to ask "What happens when you restore a SQL Server 2005 database ?" Scenario:
Users call to say that web application X is returning no data. During investigation the following SQL is run with the following result: **select * from table_a
Msg 7105, Level 22, State 6, Line 1
The Database ID 5, Page (1:81463), slot 53 for LOB data type node does not exist. This is usually caused by transactions that can read uncommitted data on a data page. Run DBCC CHECKTABLE.** So I do as I am told and run the following SQL with the following result: **DBCC CHECKTABLE('table__a')
DBCC results for 'table__a'.
Msg 8928, Level 16, State 1, Line 1
Object ID 2101582525, index ID 0, partition ID 137729312358400, alloc unit ID 137729312358400 (type In-row data): Page (1:75023) could not be processed. See other errors for details. Msg 8941, Level 16, State 2, Line 1 Table error: Object ID 2101582525, index ID 0, partition ID 137729312358400, alloc unit ID 137729312358400 (type In-row data), page (1:75023). Test (sorted [i].offset <= m_freeData) failed. Slot 1, offset 0xffff is invalid. Msg 8941, Level 16, State 2, Line 1 Table error: Object ID 2101582525, index ID 0, partition ID 137729312358400, alloc unit ID 137729312358400 (type In-row data), page (1:75023). Test (sorted [i].offset <= m_freeData) failed. Slot 1, offset 0xffff is invalid. Msg 8929, Level 16, State 1, Line 1 Object ID 2101582525, index ID 0, partition ID 137729312358400, alloc unit ID 137729312358400 (type In-row data): Errors found in off-row data with ID 75574870016 owned by data record identified by RID = (1:80165:3) Msg 8964, Level 16, State 1, Line 1 Table error: Object ID 2101582525, index ID 0, partition ID 137729312358400, alloc unit ID 71913848373575680 (type LOB data). The off-row data node at page (1:75161), slot 43, text ID 69459640320 is not referenced. Msg 8964, Level 16, State 1, Line 1 Table error: Object ID 2101582525, index ID 0, partition ID 137729312358400, alloc unit ID 71913848373575680 (type LOB data). The off-row data node at page (1:75161), slot 45, text ID 69459705856 is not referenced.** Now I prepare to run some tests before applying a fix to the production environment, so I take the most recent backup of the database and restore it to our test SQL Server 2005 environment for this application. Before I begin troubleshooting I run the following SQL on the test environment and get the following result: **DBCC CHECKTABLE('table_a') DBCC results for 'incidents_main'. There are 47782 rows in 4683 pages for object "incidents_main". DBCC execution completed. If DBCC printed error messages, contact your system administrator.** Reasonably chuffed with my good fortune but also disappointed. I do not understand what happened during the restore process to address the problem. Can anyone explain this to me ? May thanks in advance, Marlon PS If you have found this page looking for issues relating to the LOB data-type you may want to try:
http://support.microsoft.com/kb/961648/ and
http://support.microsoft.com/kb/962209/