Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Database 'msdb' cannot be opened
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Rui Wang
Starting Member

Australia
2 Posts

Posted - 07/20/2008 :  02:31:46  Show Profile  Reply with Quote
Hi,

I can’t open SQL Server Object Explorer Detail on Object Explorer window on the right. I also could not backup the existing database as well (confronting similar message). Any help to avoid reinstallation would be really appreciated.

Confronting Messages below:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Database 'msdb' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information. (Microsoft SQL Server, Error: 926)

Note this message pop up right after I connect to the Microsoft SQL server management studio.

Many thanks
Rui

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 07/20/2008 :  02:33:43  Show Profile  Visit tkizer's Homepage  Reply with Quote
Try running ALTER DATABASE to reset the status of the msdb database. In previous versions, we'd use sp_resetstatus, but according to Books Online we should be using ALTER DATABASE for SQL Server 2005.

If that doesn't work, try restoring from your backups.

You should check Event Viewer for issues so that it doesn't happen again. Run DBCC CHECKDB after you have the databases in the correct state. If corruption appears, you'll need to resolve that as well. Hopefully you've got backups.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Edited by - tkizer on 07/20/2008 02:34:46
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 07/20/2008 :  13:01:09  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
What does the SQL error log say about why the DB was marked suspect?

--
Gail Shaw
SQL Server MVP
Go to Top of Page

Rui Wang
Starting Member

Australia
2 Posts

Posted - 07/20/2008 :  15:22:12  Show Profile  Reply with Quote
Many thanks tkizer, I'll give it a try.
Rui Wang
Go to Top of Page

mdubey
Posting Yak Master

USA
133 Posts

Posted - 07/21/2008 :  10:57:23  Show Profile  Reply with Quote
The First thing you may require to Do is sp_resetstatus and if it didn't work then go ahead with Tara's suggestion and restore the previous day's backup.

You can find out why DB went to suspact mode. Does the server got rebooted or something else. You can find out the correct reason from SQL error log as well and from Event viewer.

If the error shows "SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x55555555; actual signature: 0xaaaaaaa9). It occurred during a read of page (1:2896536) in database ID 9 at offset 0x00000586530000 in file "

Just I am trying to make it easier. Please let us know what was the reasion behind that suspact mode.

Manoj
MCP, MCTS
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 07/21/2008 :  11:34:18  Show Profile  Visit tkizer's Homepage  Reply with Quote
mdubey, see my post about sp_resetstatus. Also, I already covered everything you said. Find threads to answer where someone hasn't already covered what you are going to post.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000