May 26, 2012 at 3:11 am
Hi All,
One of my databases are failing to be backed up. While taking backup it runs till 90% and then halts and fires the below error:
"System.Data.SqlClient.SqlError: Read on "<Path>" failed:
1117 (failed to retrieve text for this error. Reason 1815) (Microsoft.SqlServer.Smo)"
I tried to take script and import the data in other database but both action failed.
The DBCC CHECKDB gives below error:
Msg 8966, Level 16, State 2, Line 1
Unable to read and latch page (1:64662) with latch type SH. 1117(failed to retrieve text for this error. Reason: 1815) failed.
and on a specific table:
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:64662) allocated to object ID 1205579333, index ID 1, partition ID 72057594040483840, alloc unit ID 71855127908384768 (type LOB data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1205579333, index ID 1, partition ID 72057594040483840, alloc unit ID 71855127908384768 (type LOB data). The off-row data node at page (1:64662), slot 0, text ID 12476088320 is referenced by page (1:28807), slot 5, but was not seen in the scan.
Msg 8929, Level 16, State 1, Line 1
Object ID 1205579333, index ID 1, partition ID 72057594040483840, alloc unit ID 72057594042253312 (type In-row data): Errors found in off-row data with ID 12476088320 owned by data record identified by RID = (1:35842:75)
I tried:
1) DBCC CHECKDB REPAIR_REBUILD
2) DBCC CHECKTABLE REPAIR_ALLOW_DATA_LOSS
3) Dropped and recreated the indexes
Nothing worked. Its been a week now, so any help would be greatly appreciated.
Thanks,
Prince.
May 26, 2012 at 5:08 am
Please run the following and post the full and complete 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
May 26, 2012 at 7:18 am
Hi,
Thanks for your response. However, currently my db is in restoring state.
I found this link:
and attempted a restore from my 10 days ago backup file.
Processed 0 pages for database 'testdb', file 'testdb_Data' on file 1.
Processed 1 pages for database 'testdb', file 'testdb_Log' on file 1.
The roll forward start point is now at log sequence number (LSN) 462000008119300001. Additional roll forward past LSN 543000000046900001 is required to complete the restore sequence.
This RESTORE statement successfully performed some actions, but the database could not be brought online because one or more RESTORE steps are needed. Previous messages indicate reasons why recovery cannot occur at this point.
RESTORE DATABASE ... FILE=<name> successfully processed 1 pages in 92.195 seconds (0.000 MB/sec).
So I am just waaaaiiiittttttinnnggggggg... for the LSN 543000000046900001 to complete and to get my back to online state.
Generally how long it takes? (It has been around an hour for me it still resotring 🙁 )
Regards,
Prince.
May 26, 2012 at 7:47 am
You could wait forever, nothing will change. You need to restore one or more log backups.
Did you do a full restore or try a page restore? If you tried a page restore and have no log backups, you just did further damage to your database and probably need to drop it and restore that 10 day old backup completely.
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
May 26, 2012 at 12:47 pm
Yes, I restored a single page. However, the problem that I am facing is to take the log backup and even restoring ;
The database is stuck in RESTORING mode.
Now how to get the db out of restoring mode?
This is all acting like Murphy's law... :alien:
May 26, 2012 at 12:59 pm
Do you have an unbroken chain of log backups from that 10 day old backup right up to today? Has the DB been switched to simple recovery any time since that 10 day old 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
May 26, 2012 at 11:39 pm
😉
May 28, 2012 at 1:16 am
Now again another issue that we are facing further is on data insertion.
As mentioned above that I have another database in which there is some data migrated. I tried to make some identity inserts in my original database; so started giving me Duplicate entries error.
That means that the data is there in my original data also; but somehow it is not accessible.
Not sure what is happening over here.
May 28, 2012 at 3:48 am
Not following. Can you explain more please?
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
May 28, 2012 at 4:15 am
My fault, I was adding some IDs which were already present 🙂
Thanks for all your help Gail.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply