Database 'msdb' cannot be opened

  • mayank jarsaniya

    Hall of Fame

    Points: 3532

    Hi...

    when connecting to sql server enteprice edition 2008's named instance

    following error occur. after reading error i have check harddisk location for datafile and logfile and i found that msdb.log is not there...

    ------------ERROR---------------

    TITLE: Microsoft SQL Server Management Studio

    ------------------------------

    Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

    ------------------------------

    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    Database 'msdb' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. (Microsoft SQL Server, Error: 945)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.1600&EvtSrc=MSSQLServer&EvtID=945&LinkId=20476

    ------------------------------

    BUTTONS:

    OK

    ------------------------------

  • Gail Shaw

    SSC Guru

    Points: 1004474

    There isn't supposed to be a msdb.log file, so that's not the problem. The MSDB transction log is MSDBLog.ldf

    Can you do as the message said and check the SQL error log? In object explorer it's under Management-> SQL server logs. There'll be messages in there regarding why the db is not available. Find them and post them here.

    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
  • mayank jarsaniya

    Hall of Fame

    Points: 3532

    When i click on management tabs the error says and i cannot access error log.

    also there are no MSDBLog.ldf file present.

    TITLE: Microsoft SQL Server Management Studio

    ------------------------------

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    ADDITIONAL INFORMATION:

    Database 'msdb' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. (Microsoft SQL Server, Error: 945)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.1600&EvtSrc=MSSQLServer&EvtID=945&LinkId=20476

    ------------------------------

    BUTTONS:

    OK

    ------------------------------

  • mayank jarsaniya

    Hall of Fame

    Points: 3532

    actually cause is i lost my msdblog.ldf and that's problem, but now i have no any backup for msdb database.

    any solution to rebuild or recover from this error.or i have to reinstall again?

    when you click on any tab management/database/system database you get same posted error,

    because it cant get data from msdb .

    please advice

  • Gail Shaw

    SSC Guru

    Points: 1004474

    The error log is a text file. Just do a search for it in the OS, it'll be called ERRORLOG.

    We can probably do an emergency mode repair and rebuild the log, but I would like to see all the error messages first.

    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
  • mayank jarsaniya

    Hall of Fame

    Points: 3532

    hi,

    i have attach error log with this mail.

    but all error log says that i have lost msdblog.ldf

    i have tried but can not got logfile for msdb in my default installtion directory.

    anyway i can rebuilt my msdb database,

    Thanks,

  • Gail Shaw

    SSC Guru

    Points: 1004474

    If you're happy with rebuilding and losing all your jobs, job history, backup history, etc, then go ahead. If not, I can help you rebuild the log.

    Would you mind reposting that error log zipped? It's a little large as-is?

    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
  • Chuck Hottle

    SSCarpal Tunnel

    Points: 4777

    This may be related to an issue that I've seen in SS 2008 where non-sysadmin users need to be added as users in msdb in order to browse databases in SSMS. They don't need any explicit permissions, but do need to be users.

  • MohammedU

    SSCoach

    Points: 19644

    If you have another msdb backup from different server with same build can be restored but you loose all the history or jobs etc...

    http://blogs.msdn.com/psssql/archive/2008/08/29/how-to-rebuild-system-databases-in-sql-server-2008.aspx

    http://sqlblog.com/blogs/tibor_karaszi/archive/2008/08/27/rebuilding-msdb-on-sql-server-2008.aspx

    MohammedU
    Microsoft SQL Server MVP

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

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