none
Database 'msdb' cannot be opened

    Question

  • Anyone help?

    I opened SQL Server management studio and tried to connect with the local database and received the error message:

    Database 'msdb' cannot be opened. It has been marked suspect.....

    I have pasted the only error from the event log I could find below, but it doesn't mean much to me.

    In the event I can't find what the problem is, and not being a database administrator, can anyone tell me what I can do to get back in working order without messing up all the databases in my SQL Server 2005. I'm afraid I only have a MASTER backup from several months ago, and am afraid if I use this, I will loose the latest data. I'm a novice, and do not understand the relationship of the master/msdb/model databases to my core custom databases. Can I do some sort of master rebuild without affecting the other databases I have?

    I have tried sp_resetstatus but this didn't fix the problem, but  it did  enable me to actually connect to the SQL server instance so I could access all the other databases.

    Many thanks
    Gerard

    Event log error detail:

    The log scan number (152:284:1) passed to log scan in database 'msdb' 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.

    Tuesday, June 06, 2006 2:24 PM

Answers

All replies

  • Hi Gerard,

    There are a few things you need to do:

    1. Work out what happened to make msdb go suspect.
      • Take a look at the SQL Server errorlog (if you have a single instance installed in the default path, this will be in the 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG' directory) and the Windows application event log (use the Event Viewer from the Administrative Tools menu from Start). Look for any indications of disk or IO problems.
      • Alternatively, do you know if any unusual event happened to the server, like an unexpected power cycle? If this happened, and you have write-caching enabled on your drives, this can cause a problem.
      • Whatever happened, you should work to understand it so that you can take any necessary steps to prevent it happening again.
    2. Because the database is suspect, and msdb cannot be put into emergency mode, you're not going to be able to repair the database so you'll need to recreate it (and then recreate any scheduled jobs). Here's how to do it (I just did this myself on my laptop on SQL Server 2005):
      • Start SQL Server with trace flag 3608 (to allow you to detach msdb). I did this by shutting down SQL Server and then going to the 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn' directory and from a command prompt doing 'start sqlservr.exe -c -T3608'
      • Use the master database and detach msdb using "sp_detach_db 'msdb'"
      • Rename the damaged msdb files (msdbdata.mdf and msdblog.mdf in the 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data' directory)
      • Run the instmsdb.sql script from the 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install' directory
      • Shutdown and restart SQL Server normally
    3. I strongly recommend you get a backup strategy put together - we can help you with this on this forum.

    Let me know how you get on.

    Tuesday, June 06, 2006 5:48 PM
    Moderator
  • Hi Paul,

     

    thank you very much for your help. I've got a slightly different setup, having had SQL Server 2000 installed previously. I therefore have

    C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data

    and also have my databases in a different directory:

    C:\SQLServer\DATA\MSSQL\Data

     

    Thing I can't figure out, is why I seem to have msdbdata files in both the above directories. Which one is the one I need to rename?

    I think I know the reason for the corruption, we had a power outage a few days ago a few seconds before this pc shut down, and I think this is what has caused the problem.

    Thanks
    Gerard

    Tuesday, June 06, 2006 6:35 PM
  • You're welcome.

    I'm guessing you've got two sets of files because you installed a new SQL Server 2005 instance rather than upgrading your SQL Server 2000?

    You can find out which msdb is the SQL Server 2005 one by going into msdb before you attach it and doing 'select * from sys.sysfiles'. This will give you the pathnames of the data and log files (I think it'll be the first path you mentioned). If you've already detached the database, try renaming the files in the first path - you'll be able to if its been detached (SQL Server holds an exclusive file lock on all attached database files while its running)

    Let me know how it goes.

    Tuesday, June 06, 2006 6:57 PM
    Moderator
  • Paul,

    Did what you said and found that the C:\SQLServer\DATA\MSSQL\Data is the right one.

    I've now got it all back online thanks to you, if you were local I'd buy you a beer! Had me stressed out quite a bit today.

    I've all sorts of anomolies really, for instance, I stopped SQL in the Services applet before carrying out your changes. But when I tried to restart SQL again, I wasn't able to. So I went into SQL Mangt Studio, and went straight in, as if it was already up and running. I thought I'd see a green light somewhere like in old enterprise manager SQL 2000, but can't see one! I am sure it's all not set up properly. I thought I did a reasonable job of upgrading from 2000, (not dual install), but I think I missed something somewhere.

    My problem is, I run my own web dev shop and need to do all things from SQL server admin, to web hosting to .NET C# coding to javascript. It's hard to be master of all, and you end up being jack of all trades. I just wish I could afford a proper SQL admin guy - perhaps one day!

    There is so much I still need to do with SQL, for instance setting up a proper back-up schedule, it's just getting the time to do it all.

    Anyhow, must stop waffling on, so again, thank you very much, you helped me out a lot here!

    Gerard

    Tuesday, June 06, 2006 7:24 PM
  • Paul,

    Actually, spoke too soon. Am now getting:

     

    An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider)

     

    Have you any idea why this might be and what I can do to correct this?

    Thanks again
    Gerard

    Tuesday, June 06, 2006 7:32 PM
  • Glad I could be helpful.

    Check out the info in the KB article referenced below.

    914277 How to configure SQL Server 2005 to allow remote connections
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

    Tuesday, June 06, 2006 7:41 PM
    Moderator
  • hi,

    i had the same problem, blamed it on power interruption too.
    i tried doing the first step and it seems to be doing ok
    my problem is, i tried executing the stored proc and using query analyzer and
    there's an error "System databases...cannot be detached"

    I can't quite figure out what i did wrong.
    Thursday, September 07, 2006 11:51 AM
  • I have faced the same problem due to power failure. But in my case ‘model’ database gets corrupted. So, I had taken the model database files (detach and attach) from other server and it worked fine.

     

    Tuesday, June 17, 2008 8:52 AM
  • Hi there -

     

    I'm having the same problem with my 'msdb' database and having difficulting completing the steps outlines earlier in this thread.  I am not a SQL administrator (unfortunately); I only know how to spell SQL Wink.  I don't have any backups because this is only running on my local development machine Sad

     

    I am attempting to perform the following steps:

  • Start SQL Server with trace flag 3608 (to allow you to detach msdb). I did this by shutting down SQL Server and then going to the 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn' directory and from a command prompt doing 'start sqlservr.exe -c -T3608'
  • Use the master database and detach msdb using "sp_detach_db 'msdb'"
  • Rename the damaged msdb files (msdbdata.mdf and msdblog.mdf in the 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data' directory)
  • Run the instmsdb.sql script from the 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install' directory
  • Shutdown and restart SQL Server normally

     

    However, I'm a little lost on the 2nd step to detatch the msdb.  Do I need to start SQL Server again and run the sp_detatch_db command from the MSMS application?  I'm guessing because of the last statement.  Unfortunately, when I do restart SQL server and attempt to detach the database, I get an error telling me that the Model, MSDB and other databases cannot be detached.

     

    Am I missing something? (Likely).  Any help is appreciated.

     

    Thanks,

    Bob

Thursday, August 28, 2008 3:43 PM
  • Nevermind -

     

    I figured it out.  When you run the start command from the command prompt, it will display some messages.  DON'T KILL or close the window.  Makes all the sense in the world to me. 

     

    Thanks for all those who posted previously in this thread!

    Bob

    Thursday, August 28, 2008 3:56 PM
  • Do we have to restore all jobs again ?

    Wednesday, September 03, 2008 5:50 PM
  • Most of MS SQL database that are marked as suspect just because corruption has occured in them. And in most of such cases, these databases are unmountable, as they always display an error message instead of mounting. What you can do is follow the below steps:

    • Try and execute DBCC repair
    • Restore database from an online backup

    But if in case the database remains corrupted and you want to access the records, then use a third-pary product. One such site that provides great information about such products is mentioned below. See if that helps you:

    http://www.datarecovery-info.com/database-recovery/sql-recovery.html

     

    Saturday, March 05, 2011 7:42 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:29 AM
    Tuesday, August 16, 2011 7:28 AM
  • For anyone who has problem like Paul, you just have to make sure you have turned the SQL service back.

    Hope that helps.

    http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/

    Wednesday, September 07, 2011 12:47 AM
  • How do migrate data from old msdb database to new recreated msdb database ?

    I have a similar situation where msdb database has issues. Integrity checks fail and the database does not allow backups though its still operational. I am planning to recreate msdb database but I don't know what information is contained in msdb apart from jobs. I would appreciate if someone can let me know what information is stored in msdb and how can I export (probably generate scripts with data) and import it back after recreating msdb. Its SQL Server 2005 standard edition.


    Don

    Monday, April 02, 2012 3:38 PM
  • Dear Ravi,

    Thanks for your Post,Really it has most help full for me ....

    Sunday, September 30, 2012 6:24 AM
  • True!  In fact, it even worked for me if I just copied the same from the SQLExpress instance installed on the same box.

    Marvin


    mp

    Wednesday, March 26, 2014 5:51 PM
  • For msdb suspect issue 

    i have solution pls follow below mentioned steps to solve the issue.

    Step 1: Stop sql services and C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Template Data

    OPen this path and copy msdb log and data files from this folder and paste it to DATA folder.

    Step 2: Restart the SQL services.

    then u can use it .

    Thursday, May 15, 2014 7:48 AM