Post reply

Database 'msdb' cannot be opened

  • September 17, 2008 at 1:32 pm

    #212711

    We are currently unable to open SQL Server Management Studio and are getting the following error:

    Database 'msdb' cannot be opened. It is in the middle of a restore. (Microsoft SQL Server, Error: 927)

    This morning we were getting another error stating that msdb could not be accessed. When I noticed that the msdbDATA.mdf file was missing I attempted to restore from backup. This is when I started to see the error listed above. Now the msdb is in a state of "Restoring". I would like to stop that restore so that I may delete the msdb database and then re-create it from a script or restore it from a backup. Here's what I've tried:

    1. Restarted the SQL Server service (error remains)

    2. Rebooted server (error remains)

    3. Started service with start sqlservr.exe -c -T3608 (this allows me to browse other databases via SSMS but the msdb still shows as (Restoring...) and other DB then will stop functioning (i.e. the apps that hit these DBs will be broken).

    Due to this problem we are not able to run any scheduled jobs/backups. How can I change the status of msdb to Normal so that I might remove it, then restore from backup? Thanks for any help you can offer.

  • September 17, 2008 at 1:53 pm

    #873248

    You don't need to drop it before restoring, and I don't think it can be deleted.

    Here's an msdn article on restoring MSDB

    http://msdn.microsoft.com/en-us/library/ms190749(SQL.90).aspx

    Before trying all that, try just running

    RESTORE DATABASE MSDB WITH RECOVERY

    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

  • September 17, 2008 at 7:07 pm

    #873333

    Thanks for your reply. That was actually one of the first things I tried. I started the service with "start sqlservr.exe -c -T3608" and then I was able to access SSMS and run queries so I ran "RESTORE DATABASE MSDB WITH RECOVERY" and I get the following error:

    Msg 4333, Level 16, State 1, Line 1

    The database cannot be recovered because the log was not restored.

    Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.

    I'd seen that article as well but we haven't gotten to the point yet where we can restore. I think we need to find some manual way to get SQL Server to realize that msdb no longer exists and that it is not in a restoring state. I'm going to look into status codes. Please let me know if you have any other suggestions.

  • September 18, 2008 at 1:24 am

    #873413

    I think you're going to have to restore it from a backup. SQL won't run properly without MSDB and, since it's a system database you can't just drop it.

    I'd seen that article as well but we haven't gotten to the point yet where we can restore.

    Why? What's preventing a restore?

    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

  • September 18, 2008 at 5:50 am

    #873526

    Any attempt at a restore fails with the message:

    Database 'msdb' cannot be opened. It is in the middle of a restore. (Microsoft SQL Server, Error: 927)

  • September 18, 2008 at 5:56 am

    #873530

    Via T-SQL or through management studio GUI? If the latter, try it via T-SQL. Management studio may be trying to query the DB before starting the restore.

    If via T-SQL, what's the command you're using for the restore?

    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

  • September 19, 2008 at 7:57 am

    #874227

    We weren't able to do anything with msdb via SSMS or t-sql and it was discovered that tempdb was hosed as well. I ended up just detaching the user DBs, uninstalling/reinstalling SQL Server and then re-attaching the user DBs. Everything is good now.

  • September 22, 2008 at 1:17 am

    #874810

    Have you discovered that why it was giving msdb related error ??

    you can do it with the help of error log

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • September 22, 2008 at 6:20 am

    #874912

    The error log got wiped when I reinstalled SQL Server. Once this problem started I wasn't able to take any backups so I'm not sure how I'd recover the log during that problem period. I guess all's well that ends well. Thanks again for your comments.

  • October 19, 2009 at 9:37 am

    #1067716

    I am getting the same error but I think my cause is self induced. I was attempting to use a database copy scenario for DR solution. As opposed to restoring databases I was able to automate a copy of the actual MDF/LDF sets for each database on the prod server over to a DR server. So I basically stopped SQL, overwrote all the files, and restarted SQL. All system and user databases came up fine with the exception of MSDB. I've tried several times with different copies of the MSDB files and the result is always the same.

    Does anyone know why MSDB is having this issue and nothing else?


    thanks, ERH

  • October 19, 2009 at 9:59 am

    #1067727

    Eddie Hendrix (10/19/2009)

    Does anyone know why MSDB is having this issue and nothing else?

    What are the messages in the error log relating to MSDB?

    Please in future can you start a new thread for a new problem? Thanks.

    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

  • October 20, 2009 at 1:21 am

    #1067993

    I faced a similar problem in the recent past. Mine was most probably due to disk corruption cant say for sure. I tried everything that you have listed and it did not work. Ultimately i had to do a re-install.

    "Keep Trying"

  • October 27, 2009 at 1:17 pm

    #1071415

    The error:

    An error occurred during recovery, preventing the database 'msdb' (database ID 4) from restarting. Daignose teh recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contract Technical Support

    I am Technical Support.....;-)


    thanks, ERH

  • October 27, 2009 at 2:26 pm

    #1071446

    There should be more messages than that in the log. Post the entire thing if you're unsure.

    Recommendation: As the error message says, restore from backup.

    Technical support, in this case, refers to Microsoft's Customer Support people

    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

  • October 28, 2009 at 2:57 pm

    #1072092

    I appreciate the help but I guess my question is more general than asking you to help debug the fine points of my problem.

    The basic question is: should a valid set of .MDF/.LDF files of the MSDB database be able to migrate, and come online, when moved from server to server?

    I can still provide the error log if your curious.


    thanks, ERH

Viewing 15 posts - 1 through 15 (of 15 total)

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