Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Database 'msdb' cannot be opened Expand / Collapse
Author
Message
Posted Friday, January 1, 2010 4:20 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 24, 2014 1:16 AM
Points: 347, Visits: 412
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
------------------------------
Post #841048
Posted Friday, January 1, 2010 7:00 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 4:54 PM
Points: 42,434, Visits: 35,488
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 2008, MVP
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

Post #841065
Posted Sunday, January 3, 2010 10:59 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 24, 2014 1:16 AM
Points: 347, Visits: 412
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
------------------------------
Post #841305
Posted Sunday, January 3, 2010 11:05 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 24, 2014 1:16 AM
Points: 347, Visits: 412
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




Post #841306
Posted Sunday, January 3, 2010 11:49 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 4:54 PM
Points: 42,434, Visits: 35,488
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 2008, MVP
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

Post #841314
Posted Monday, January 4, 2010 6:03 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 24, 2014 1:16 AM
Points: 347, Visits: 412
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,


  Post Attachments 
errorlog.txt (18 views, 1.39 MB)
Post #841408
Posted Monday, January 4, 2010 8:21 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 4:54 PM
Points: 42,434, Visits: 35,488
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 2008, MVP
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

Post #841510
Posted Tuesday, January 5, 2010 9:29 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:45 AM
Points: 198, Visits: 1,126
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.
Post #842181
Posted Tuesday, January 5, 2010 1:38 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 6:35 PM
Points: 1,190, Visits: 582
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
Post #842387
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse