Database 'msdb' cannot be opened

Hot Topic

Sort by month
about DTS
List of new supported datatypes
SQL Server Express "Total" Edition
database maintenance plan - reorganize data pages and indexes
Access Permissions on server scoped objects for login
SQL Profiler and Analysis Services Connection Error
Problem with non-standard dimension
Help - Standard vs. Data-Driven Subscriptions
SQL 2005 SP1 on Win2003 R2 - Is it supported/ certified?

SQL Server

Random Failure of Lookup in Pre-Execute with 0x8007000E
Why not ADO.NET datatables inside?
Cannot start the Reporting Service
Killer problem with Scheduled Reports
The connection manager 'OLEDB' is not properly installed on this computer
SUM problem in SSRS 2005 report
Failure to run when using DTC
Returns Nothing, But Executes Fine
Data format conversion.
test - ignore

Database 'msdb' cannot be opened

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.



Answer this question

Database 'msdb' cannot be opened

  • Ignazio

    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.



  • husam_aCheR

    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

  • Goran M

    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.

  • Parminder

    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



  • tomdeloford

    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.



  • MrRuffian

    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

  • Goran Verush

    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
  • Database 'msdb' cannot be opened
Answer this question