suspect database after san failure

  • Sunil Padarath

    SSC Eights!

    Points: 832

    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)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

    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)

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714080

    Call PSS at Microsoft. They can help you work through this. If you have corruption, I hope you have backups somewhere else.

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Sunil Padarath

    SSC Eights!

    Points: 832

    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

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Sunil Padarath

    SSC Eights!

    Points: 832

    thanks. i will let you know once i do this

  • Sunil Padarath

    SSC Eights!

    Points: 832

    The new db which i will create has to be the same name of the damaged db or a totaly new named db?

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Sunil Padarath

    SSC Eights!

    Points: 832

    OK, i did as you had requested and the DB is now in suspect mode.

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Sunil Padarath

    SSC Eights!

    Points: 832

    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.

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Sunil Padarath

    SSC Eights!

    Points: 832

    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.

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Sunil Padarath

    SSC Eights!

    Points: 832

    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 36 total)

You must be logged in to reply to this topic. Login to reply