May 12, 2010 at 3:03 pm
am getting error (below) when trying to attach database card after san failure. appreciate any help we could get.
Attach database failed for Server 'kadavu05\test'. (Microsoft.SqlServer.Smo)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The log scan number (78939:11140:1) passed to log scan in database 'card' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
Could not open new database 'card'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 9003)
May 12, 2010 at 3:16 pm
Call PSS at Microsoft. They can help you work through this. If you have corruption, I hope you have backups somewhere else.
May 12, 2010 at 3:40 pm
Restore from backup.
Your transaction log is damaged and the database was not cleanly shut down. SQL's hitting the corruption while trying to do it's restart-recovery (which is essential to get the DB back into a consistent state) and marking the database suspect.
If you don't have a clean backup, fixing this is still possible (though not certain), but it will likely lose data and may have unpleasant long-term consequences.
If you don't have a backup, I'll walk you through getting the thing back if possible tomorrow morning (my time)
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 12, 2010 at 3:48 pm
this was in production environment and we do not have a backup. would appreciate your assistance to work us through this at your time.
in meantime if there anything you recommend we try
thanks
May 13, 2010 at 2:12 am
Sunil Padarath (5/12/2010)
this was in production environment and we do not have a backup.
Why the hell not? How can you, as the DBA, possibly justify having no backups of a production database?
in meantime if there anything you recommend we try
Update your resume....
This is a nasty situation. Detached, suspect database. The following may work. It also may not and, if it doesn't, there are no more options left. This is the absolute last resort and, honestly, should never be needed (because you should have backups)
I make absolutely no guarantees about this working or recovering your database.
Create a new database on the SQL instance. Make sure that it has the same number of files (and I think, logical name for them) as the DB that you cannot attach. Make sure that you don't overwrite the files of the suspect database.
Stop the SQL instance.
Swap the files of the database that you just created for the one that won't attach. Make sure that you get them all correct.
Restart SQL. The database should be listed in the database list, but will probably be suspect.
Tell me once you get to this point. Do not do anything else to the DB!
p.s. You have checked that the SAN is ok now?
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 13, 2010 at 2:21 am
thanks. i will let you know once i do this
May 13, 2010 at 2:23 am
The new db which i will create has to be the same name of the damaged db or a totaly new named db?
May 13, 2010 at 2:24 am
Preferably same name.
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 13, 2010 at 2:30 am
OK, i did as you had requested and the DB is now in suspect mode.
May 13, 2010 at 2:32 am
Open the SQL error log, find all errors relating to this DB (from the time of the attach) and post them.
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 13, 2010 at 2:53 am
The log scan number (78939:11140:1) passed to log scan in database 'card' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
Message
An error occurred during recovery, preventing the database 'card' (database ID 17) 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.
Error: 3414, Severity: 21, State: 1.
Error: 9003, Severity: 20, State: 9.
Error: 3414, Severity: 21, State: 1.
Error: 928, Severity: 20, State: 1.
During upgrade, database raised exception 926, severity 14, state 1, address 00000000017238A8. Use the exception number to determine the cause.
Setting database option SINGLE_USER to ON for database card.
Setting database option SINGLE_USER to ON for database card.
May 13, 2010 at 3:00 am
Upgrade? Please don't tell me that you're trying to attach this to a different version of SQL to what it was originally attached.
Messages and error numbers appear mangled. Please post the exact entries from the log, in the order that they occur, from the start of the attach up until now.
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 13, 2010 at 3:14 am
Error: 9003, Severity: 20, State: 9.
Error: 3414, Severity: 21, State: 1.
Error: 928, Severity: 20, State: 1.
During upgrade, database raised exception 926, severity 14, state 1, address 00000000017238A8. Use the exception number to determine the cause.
Setting database option SINGLE_USER to ON for database card.
Setting database option SINGLE_USER to ON for database card.
Message
The log scan number (78939:11140:1) passed to log scan in database 'card' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
An error occurred during recovery, preventing the database 'card' (database ID 17) 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.
May 13, 2010 at 3:20 am
That's not how they'd appear in the log (msg number would be folowed by the message itself), but I suppose good enough.
Now, about that upgrade message. Please don't tell me that you're trying to attach this to a higher version of SQL Server than it was originally attached to.
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 13, 2010 at 3:21 am
Actualy the logs have been over-written as we have been trying out some cammands like:
sp_resetstatus
alter database card set emergency
dbcc checkdb
alter database card set single_user
dbcc checkdb (card, repair_allow_data_loss)
sp_configure 'allow_updates', 1 Reconfigure with overide
unfortantly dbcc checkdb and dbcc checkdb (card, repair_allow_data_loss) command did not seem to work. The db was in recovery mode for more than 15 hours.
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply