none
How to recover msdb database from suspect mode.

    Question

  • I have tried the following STEPS OF  commands to recover but in vain.

    STEP 1) EXEC sp_resetstatus 'MSDB'

     o/p Message :The suspect flag on the database "MSDB" is already reset.

    STEP 2)ALTER DATABASE MSDB SET EMERGENCY

     O/P MESSAGE : Msg 5058, Level 16, State 6, Line 1  ,Option 'EMERGENCY' cannot be set in database 'MSDB'.
    STEP 3) DBCC checkdb('MSDB')
     O/P Message : Location: page.cpp:3364
    Expression: GetGhostRecCount () > 0
    SPID: 53
    Process ID: 1476
    Msg 926, Level 14, State 1, Line 1
    Database 'msdb' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
    Msg 3624, Level 20, State 1, Line 1
    A system assertion check has failed. Check the SQL Server error log for details
    Msg 3313, Level 21, State 2, Line 1
    During redoing of a logged operation in database 'msdb', an error occurred at log record ID (539579:376:3). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
    Msg 3414, Level 21, State 1, Line 1
    An error occurred during recovery, preventing the database 'msdb' (database ID 4) 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.
    step 4) ALTER DATABASE MSDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
     o/P Message :
    Location: page.cpp:3364
    Expression: GetGhostRecCount () > 0
    SPID: 51
    Process ID: 1476
    Msg 926, Level 14, State 1, Line 1
    Database 'msdb' 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 3624, Level 20, State 1, Line 1
    A system assertion check has failed. Check the SQL Server error log for details
    Msg 3313, Level 21, State 2, Line 1
    During redoing of a logged operation in database 'msdb', an error occurred at log record ID (539579:376:3). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
    Msg 3414, Level 21, State 1, Line 1
    An error occurred during recovery, preventing the database 'msdb' (database ID 4) 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.
    STEP 5) DBCC CheckDB ('MSDB', REPAIR_ALLOW_DATA_LOSS)
     O/P Message : Msg 926, Level 14, State 1, Line 1
     Database 'msdb' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
    step 6) ALTER DATABASE MSDB SET MULTI_USER
     O/P Message :
    Location: page.cpp:3364
    Expression: GetGhostRecCount () > 0
    SPID: 51
    Process ID: 1476
    Msg 926, Level 14, State 1, Line 1
    Database 'msdb' 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 3624, Level 20, State 1, Line 1
    A system assertion check has failed. Check the SQL Server error log for details
    Msg 3313, Level 21, State 2, Line 1
    During redoing of a logged operation in database 'msdb', an error occurred at log record ID (539579:376:3). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
    Msg 3414, Level 21, State 1, Line 1
    An error occurred during recovery, preventing the database 'msdb' (database ID 4) 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.
    CAN ANYBODY HELP OUT  ME TO RECOVER msdb FROM REVEOR MODE...
    THANKS In Advance

    • Moved by Papy Normand Saturday, August 06, 2011 12:27 PM Problem of recover of a suspect msdb not a Data Access problem (From:SQL Server Data Access)
    Friday, August 05, 2011 6:17 PM

Answers

  • Hello,

    I don't think that this thread is in the correct forum, i will try to find a better suitable forum.

    Anyway, do you know this link ?

    http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/06/619304.aspx

    Even it is for SQL Server 2005, it could be helpful.

    Have a nice day


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
    • Proposed as answer by Deepesh_MSDN Saturday, August 06, 2011 12:44 PM
    • Marked as answer by Peja Tao Monday, August 15, 2011 8:50 AM
    Saturday, August 06, 2011 12:18 PM
  • Sureshkumar,

    The best approach in this situation is to restore MSDB from the last good backup.




    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @PradeepAdiga

    • Proposed as answer by Abhay_78 Saturday, August 06, 2011 4:57 PM
    • Marked as answer by Peja Tao Monday, August 15, 2011 8:50 AM
    Saturday, August 06, 2011 2:31 PM
  • Hi  Suresh,

    As when you ran DBCC Checkdb, you got to know that your MSDB database got corrupted.

    As you see from the error log:

    Msg 3313, Level 21, State 2, Line 1
    During redoing of a logged operation in database 'msdb', an error occurred at log record ID (539579:376:3). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
    Msg 3414, Level 21, State 1, Line 1
    An error occurred during recovery, preventing the database 'msdb' (database ID 4) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup.

    This suggests you to either repair or restore your database to fix the issue.Since this being SYSTEM database , I would recommend you to RESTORE the MSDB database from the last known valid full msdb database backup file.

    Hope this will resolve your issue!!


    Regards, Vishal Srivastava
    • Proposed as answer by Srivastava Vishal Sunday, August 07, 2011 4:53 AM
    • Marked as answer by Peja Tao Monday, August 15, 2011 8:51 AM
    Sunday, August 07, 2011 4:53 AM

All replies

  • Hi Sureshkumar

    Try with this..

    sp_configure 'allow update options',1
    reconfigure with override

    update the sysdatabases status column with the value -32768 for msdb database

    the msdb will go into emergency mode.

    Migrate the table data to from msdb database to another database with different name.

    now try to execute sp_resetstatus system procedure on the database msdb.

    rerun sp_configure 'allow update options',0
    reconfigure with override


    Nag Pal MCTS/MCITP (SQL Server 2005/2008) :: Please Mark Answer/vote if it is helpful ::
    Saturday, August 06, 2011 12:25 AM
  • Hi Nag Pal

    Thanks for your reply. 

     I have tried the following commands 

    1) EXEC sp_configure 'allow updates', 1  reconfigure with override

    2) update sysdatabases set status = 32768 where name = 'msdb'

    For the second update i got error message like  "  Msg 259, Level 16, State 1, Line 1 Ad hoc updates to system catalogs are not allowed."

     

    Can you help as its very very critical situation  for me to recover MSDB from suspect mode.

     

    Thanks

    Suresh Kumar

     

    Saturday, August 06, 2011 7:18 AM
  • Hello,

    I don't think that this thread is in the correct forum, i will try to find a better suitable forum.

    Anyway, do you know this link ?

    http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/06/619304.aspx

    Even it is for SQL Server 2005, it could be helpful.

    Have a nice day


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
    • Proposed as answer by Deepesh_MSDN Saturday, August 06, 2011 12:44 PM
    • Marked as answer by Peja Tao Monday, August 15, 2011 8:50 AM
    Saturday, August 06, 2011 12:18 PM
  • Hello,

    I give a link towards threads similar to this one :

    http://social.msdn.microsoft.com/Search/en-US/sqlserver?query=recover%20msdbsuspect&rq=meta:Search.MSForums.GroupID(1794d07f-9d4e-4dc0-8c1c-8bfe9d5e0bce)+site:microsoft.com&rn=All+SQL+Server+Forums

    I will move this thread towards the SQL Server Database Engine Forum which seems ( for me ) more appropriate than the SQL Server Data Access which is more dedicated to connectivity problems.

    http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/threads

    Have a nice day

     


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
    Saturday, August 06, 2011 12:25 PM
  • Hello,

    As i have understood that you are in an emergency situation, i have decided to move this thread in this new forum without asking you your agreement to shorten the answers delay.

    I hope you will find a quick ( and not too complicated ) answer in this forum.

    Good luck and have a nice day


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
    Saturday, August 06, 2011 12:31 PM
  • Sureshkumar,

    The best approach in this situation is to restore MSDB from the last good backup.




    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @PradeepAdiga

    • Proposed as answer by Abhay_78 Saturday, August 06, 2011 4:57 PM
    • Marked as answer by Peja Tao Monday, August 15, 2011 8:50 AM
    Saturday, August 06, 2011 2:31 PM
  • Hi  Suresh,

    As when you ran DBCC Checkdb, you got to know that your MSDB database got corrupted.

    As you see from the error log:

    Msg 3313, Level 21, State 2, Line 1
    During redoing of a logged operation in database 'msdb', an error occurred at log record ID (539579:376:3). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
    Msg 3414, Level 21, State 1, Line 1
    An error occurred during recovery, preventing the database 'msdb' (database ID 4) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup.

    This suggests you to either repair or restore your database to fix the issue.Since this being SYSTEM database , I would recommend you to RESTORE the MSDB database from the last known valid full msdb database backup file.

    Hope this will resolve your issue!!


    Regards, Vishal Srivastava
    • Proposed as answer by Srivastava Vishal Sunday, August 07, 2011 4:53 AM
    • Marked as answer by Peja Tao Monday, August 15, 2011 8:51 AM
    Sunday, August 07, 2011 4:53 AM
  • The other very simple steps to recover the MSDB database from suspect mode is :-

    1. Stop the SQL Server database & services

    2. Rename or move the "msdblog.ldf" & "msdbdata.mdf" files

    3. Copy "msdblog.ldf & msdbdata.mdf" files from any other working database installation to the same path

    4. Start the SQL Server database 

    This will make your MSDB up & running
    :) hope this helps

    Regards,
    Ravi Thapliyal


    • Proposed as answer by Ravi Thapliyal Tuesday, August 16, 2011 7:28 AM
    Tuesday, August 16, 2011 7:27 AM
  • Muchas gracias por tu comentario, tome los archivos de otro servidor y levantó mi servicio sin problema, jamás se me hubiera ocurrido!

    Saludos!

    Tuesday, April 10, 2012 6:24 AM
  • Thank You Ravi!!!!!  I have spent weeks trying everything I could find on GOOGLE with no avail..  I followed your steps and ALL my instances of SQL are working again.  They were all in Suspect mode, and it was a very frustrating time trying to fix them until I found your post.  Thanks!!
    Wednesday, May 09, 2012 10:27 PM
  • Thank You Ravi!!!!!  I have spent weeks trying everything I could find on GOOGLE with no avail..  I followed your steps and ALL my instances of SQL are working again.  They were all in Suspect mode, and it was a very frustrating time trying to fix them until I found your post.  Thanks!!
    You've also lost all of your SQL Agent Jobs, History, BackupHistory, any SSIS packages and Maintenance Plans, database mail configuration, and a ton of other stuff that is incredibly important to SQL Server.  You should always have a backup of the system databases, just like the user databases to protect against problems like this.

    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    Wednesday, May 09, 2012 10:34 PM
    Moderator
  • Yes lesson learned.  I did not think I needed a 'Backup" plan for just a single PC.. but now I know better.

    Thanks!

    Wednesday, May 09, 2012 10:38 PM
  • Don't even invest time to do any surgery. Just try to find last good database backup & restore the same.
    • Proposed as answer by anuragsh Thursday, May 10, 2012 2:42 PM
    Thursday, May 10, 2012 2:42 PM
  • Thank you, this really worked for me.
    Thursday, August 09, 2012 1:59 AM
  • I don't have backup MSDB data(never happened such a problem in 10 years)

    I copied over MSDB  files as Ravi suggested from ..\MSSQL\Binn\Templates and it solved the error.

    Thanks Ravi!

    Friday, August 02, 2013 3:28 PM
  • My solution was in between.  Working with Ravi's suggestion, I copied to the current server a 2-week old backup of the 2 MSDB files.  There were no known big changes in between.  This saved me time.  A Big "Thank You" to everyone!
    Sunday, September 29, 2013 2:56 PM
  • I suggest you to check the integrity of the database first. For this you should check this link

    http://technet.microsoft.com/en-us/library/ms176064.aspx

    Once you performed that and come to the conclusion that the integrity of the database is indeed comprised then I suggest you to go through this link to rectify and correct your database.

    http://gallery.technet.microsoft.com/SQL-Data-Recovery-Process-24b861a7

    Tuesday, October 22, 2013 4:37 PM