November 19, 2010 at 3:49 am
The database xyz_1 is on Single node cluster we are still in the process of building other node as of now on one node datbase is online but something happend and datbase is in suspect mode now
Errorlog details as follows
2010-11-11 22:01:21.12 spid16s Error: 9001, Severity: 21, State: 4.
2010-11-11 22:01:21.12 spid16s The log for database 'xyz_1' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
2010-11-11 22:01:21.16 spid16s Unknown status of commit of a two-phase commit transaction. Shutting down server. Restart server to complete recovery.
2010-11-11 22:01:28.34 Server Microsoft SQL Server 2005 - 9.00.4035.00 (X64)
Nov 24 2008 16:17:31
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
2010-11-11 22:01:28.34 Server (c) 2005 Microsoft Corporation.
2010-11-11 22:01:28.34 Server All rights reserved.
The error started agin after the recovery
2010-11-11 22:01:53.77 spid39s Error: 5125, Severity: 24, State: 2.
2010-11-11 22:01:53.77 spid39s File 'I:\Data\xyz_1.mdf' appears to have been truncated by the operating system. Expected size is 6067712 KB but actual size is 5608328 KB.
when I tried putting the database in emergency mode and tried dbcc REPAIR_ALLOW_DATA_LOSS
this is the warning message I get
The log for database 'xyz_1' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.
Please help me in resolving this issue as it is a production server and I am still not able find any solutions 🙁
Thanks
November 19, 2010 at 4:06 am
Do you have a backup of your database?
John
November 19, 2010 at 4:22 am
Restore from backup and do some investigation of the IO subsystem.
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
November 19, 2010 at 6:46 am
have some old backup not recent though
November 19, 2010 at 7:06 am
You have no recent backup of a production database???? Whoever's the DBA there is not doing their job.
Ok, the message you posted after the emergency mode repair is normal after a repair rebuild log. Can you set the database to online?
Alter database <db name> Set Online
Are there any further messages in the SQL error log?
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
November 19, 2010 at 8:29 am
Unfortunately a junior DBA who got access to the server disabled the backup for some reason and forgot to enable....
yes I did run
alter database xyz_1 set online but the following message displayed
Msg 926, Level 14, State 1, Line 1
Database 'xyz_1' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5125, Level 24, State 2, Line 1
File 'G:\Data\xyz_1.mdf' appears to have been truncated by the operating system. Expected size is 73400320 KB but actual size is 72963144 KB.
Msg 3414, Level 21, State 1, Line 1
An error occurred during recovery, preventing the database 'xyz_1' (database ID 6) 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.
ya there is one more error in the log
2010-11-11 21:50:33.45 spid57 The operating system returned error 38(error not found) to SQL Server during a read at offset 0x00000147c90000 in file 'G:\Data\xyz_1.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Thanks!
November 19, 2010 at 8:39 am
Restore from backup. This is not repairable. The last backup that you have is the only real option you have remaining at this point.
Perhaps both the junior DBA and whoever was supposed to check the backups should start updating their resumes at this point. There is NO good excuse for backups not running, the disabled job should have been picked up within a day or two.
In addition, do some diagnoses on that IO subsystem. Something is not right and, unless you find and fix the root cause, this (excluding the missing backups) could happen again.
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
November 19, 2010 at 8:53 am
Thanks a lot Gail.
I tried detaching and attaching but got error while attaching
error:
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'xyz_1'. CREATE DATABASE is aborted.
Msg 5125, Level 24, State 2, Line 1
File 'G:\Data\xyz_1.mdf' appears to have been truncated by the operating system. Expected size is 15204352 KB but actual size is 15111224 KB.
November 19, 2010 at 9:08 am
Sqlsavy (11/19/2010)
I tried detaching and attaching but got error while attaching
Quite frankly that was stupid. Once detached a suspect database cannot be reattached without a complex workaround. Never, never, never detach a suspect or corrupt database
My previous statement stands. Restore from your last good backup. The corruption that caused the suspect state is NOT repairable.
p.s. Are you editing those error messages? The sizes are changing for each message. That doesn't bode well if you're not editing and those all belong to the same 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
November 19, 2010 at 9:20 am
GilaMonster (11/19/2010)
Restore from backup. This is not repairable. The last backup that you have is the only real option you have remaining at this point.Perhaps both the junior DBA and whoever was supposed to check the backups should start updating their resumes at this point. There is NO good excuse for backups not running, the disabled job should have been picked up within a day or two.
In addition, do some diagnoses on that IO subsystem. Something is not right and, unless you find and fix the root cause, this (excluding the missing backups) could happen again.
In which order do you recommend they do this? Restore old backup first? Or update resume first?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
November 19, 2010 at 9:23 am
Alvin Ramard (11/19/2010)
In which order do you recommend they do this? Restore old backup first? Or update resume first?
1) Update resume
2) Inform management of the situation (including the reason there are no recent backups and the length of time that no one noticed there were no backup)
3) (If you still have a job) Restore the last good 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
November 19, 2010 at 9:36 am
GilaMonster (11/19/2010)
Alvin Ramard (11/19/2010)
In which order do you recommend they do this? Restore old backup first? Or update resume first?1) Update resume
2) Inform management of the situation (including the reason there are no recent backups and the length of time that no one noticed there were no backup)
3) (If you still have a job) Restore the last good backup.
I know my comment was a bad joke, but I still can't believe how many posts we see on here that involve databases that are not backed up. There's just no excuse for not having backup jobs and monitoring these jobs.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
November 19, 2010 at 10:20 am
Alvin Ramard (11/19/2010)
GilaMonster (11/19/2010)
Alvin Ramard (11/19/2010)
In which order do you recommend they do this? Restore old backup first? Or update resume first?1) Update resume
2) Inform management of the situation (including the reason there are no recent backups and the length of time that no one noticed there were no backup)
3) (If you still have a job) Restore the last good backup.
I know my comment was a bad joke, but I still can't believe how many posts we see on here that involve databases that are not backed up. There's just no excuse for not having backup jobs and monitoring these jobs.
I absolutely agree. There should be a process that monitors all servers, and part of this is ensuring that the backups were performed according to whatever schedule that they should be backed up on. You should go no more than one day without having this process notifying you that backups aren't being performed. What we've currently using is the "server overview" process detailed in articles on this site - it's free, runs within SSIS, just takes some time to get it set up. Sends us a daily email link to a spreadsheet, where we can see everything we desire.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 19, 2010 at 10:49 am
I have a daily and weekly summary that gets e-mailed to me before I arrive in the morning. Outlines what backups were taken, other auto-maintenance performed by a variety of jobs and tools, and so on.
If I don't get the summary, I know something's wrong.
If it says "no backups taken", I know something's wrong.
And so on.
Makes it really easy to keep on top of this kind of thing.
But it sounds to me like you (the original post) are SOL on this database. You're going to have to recover from an old backup and explain to the management why none of the DBAs there (I gather there's more than one) were doing their jobs correctly. I hope the database isn't business-critical, as in losing it shuts down the company. Good luck and do what you can to make the best of it, even if that's just learning from this.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 19, 2010 at 11:27 am
GSquared (11/19/2010)
I have a daily and weekly summary that gets e-mailed to me before I arrive in the morning.
We have a daily "failed jobs" job that notifies us when jobs fail. We also test our prod backups daily by restoring them down out our QC server. People use these "production copies" everyday, so we know within hours if a backup from the night before failed or didn't happen.
This doesn't help you with the current situation, but if you implement similar logging to what GSquared and I have, plus implement daily restores down to a Dev/Test/QC environment, the chances of the "no backup" situation happening again are low.
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply