March 3, 2014 at 5:24 am
Hi Everyone,
Over the weekend one of our SQL Server stopped and now one of the archive databases is flagged as suspect. It does not appear that restoring this database from a backup is an option.
I tried to run a dbcc dbrecover on the database but unfortunately got the following error....
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 5159, Level 24, State 11, Line 1
Operating system error 1117(failed to retrieve text for this error. Reason: 15105) on file "F:\XXXX_Log.ldf" during CheckLogBlockReadComplete.
Msg 3414, Level 21, State 1, Line 1
An error occurred during recovery, preventing the database 'xxxxx' (database ID 12) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
any ideas on best next steps.
Regards
David
March 3, 2014 at 5:25 am
Restore from backup, that's the best approach by far, may be the only one.
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
March 3, 2014 at 5:38 am
Thanks Gail for your reply, unfortunately it does not appear to be an option in this situation 🙁
Regards
David
March 3, 2014 at 5:40 am
Why not?
What's the exact state of the database (from sys.databases)?
What are the errors in the error log relating to this DB (from just after the restart)?
How critical is this database?
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
March 3, 2014 at 5:52 am
Hi Gail,
The reason returning to a back up does not appear to be an option is that, the person I am helping cannot determine the correct backup file for this database.
Looking at the Error log I can see issues in regard to I/O taking a long time
SQL Server has encounter 2 occurances of I/O requests taking longer than 15 seconds to complete on file xxxx in database xxxx. The OS file handle is 0x000000000000578. The offset of the latest long I/0 is 0x000003d50e5600
Then is simply reports the messages in the first post.
Regards
David
March 3, 2014 at 5:58 am
That's not an error message, that's informational.
Please can you answer my questions. I really need to see all and every message relating to that database in the SQL error log. If you're not sure, just post the whole thing, the errorlog from after the restart over the weekend.
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
March 3, 2014 at 6:08 am
Hi Gail,
These are the errors relating to the DB in the Errorlog...
2014-03-03 01:17:27.53 spid21s Error: 5159, Severity: 24, State: 11.
2014-03-03 01:17:27.53 spid21s Operating system error 1117(failed to retrieve text for this error. Reason: 15105) on file "F:\DBNAME Logs\DBNAME_Log.ldf" during CheckLogBlockReadComplete.
2014-03-03 01:17:27.56 spid21s Error: 3414, Severity: 21, State: 1.
2014-03-03 01:17:27.56 spid21s An error occurred during recovery, preventing the database 'DBNAME' (database ID 12) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
2014-03-03 01:17:27.63 spid7s Recovery is complete. This is an informational message only. No user action is required.
2014-03-03 01:17:27.77 spid80 A read of the file 'F:\EDatabasename\EDatabasename.mdf' at offset 0x00000000854000 succeeded after failing 1 time(s) with error: 1117(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
2014-03-03 01:17:27.78 spid79 A read of the file 'F:\EDatabasename\EDatabasename.mdf' at offset 0x00000000854000 succeeded after failing 1 time(s) with error: 1117(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
2014-03-03 01:17:27.78 spid81 A read of the file 'F:\EDatabasename\EDatabasename.mdf' at offset 0x00000000854000 succeeded after failing 1 time(s) with error: 1117(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
2014-03-03 01:17:29.00 spid81 Attempting to load library 'C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\xp_ndo_x64.dll' into memory. This is an informational message only. No user action is required.
March 3, 2014 at 6:10 am
TerrenceTheCat (3/3/2014)
Hi Gail,The reason returning to a back up does not appear to be an option is that, the person I am helping cannot determine the correct backup file for this database.
what does this mean exactly?
This SQL will tell you the locations of backups and when they were taken
select a.database_name,a.type,user_name,a.backup_finish_date,b.physical_device_name
from msdb..backupset a inner join msdb..backupmediafamily b
on a.media_set_id = b.media_set_id
where database_name = 'yourdb'
order by backup_finish_date desc
---------------------------------------------------------------------
March 3, 2014 at 6:15 am
Definitely not good.
GilaMonster (3/3/2014)
What's the exact state of the database (from sys.databases)?
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
March 3, 2014 at 6:20 am
Have you tried using EMERGENCY mode and using DBCC CHECKDB to rebuild the log and make the database accessible (albeit transactionally inconsistent)? That's your last resort in this situation.
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
March 3, 2014 at 10:11 am
Thanks everyone for your help today, your guidance and input is very much appreciated as always. The Database was created from a full file backup copied from another database on a different server over a year ago.
I have placed the database in Emergency mode and I am currently performing a repair DBCC CHECKDB
I will let you know how I get on.
Best regards
David
March 3, 2014 at 10:19 am
TerrenceTheCat (3/3/2014)
Thanks everyone for your help today, your guidance and input is very much appreciated as always. The Database was created from a full file backup copied from another database on a different server over a year ago.
Good luck.
On the remote chance the file used for the restore is still around and your restore history has not been purged the following tells you where databases were restored from
SELECT [rs].[destination_database_name],
[rs].[restore_date],
[bs].[backup_start_date],
[bs].[backup_finish_date],
[bs].[database_name] as [source_database_name],
[bmf].[physical_device_name] as [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf
ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [rs].[restore_date] DESC
---------------------------------------------------------------------
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply