Database 'msdb' cannot be opened

Question

Database 'msdb' cannot be opened

mayank jarsaniya

Hall of Fame

Points: 3532
More actions
January 1, 2010 at 4:20 am

#386032

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
------------------------------

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

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: 1004504 More actions · Answer 4

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 More actions · Answer 5

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: 1004504 More actions · Answer 6

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 More actions · Answer 7

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 More actions · Answer 8

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