Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Rui Wang
Starting Member
2 Posts |
 Posted - 2008-07-20 : 02:31:46
|
| 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 thanksRui |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-07-20 : 02:33:43
|
| 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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-07-20 : 13:01:09
|
| What does the SQL error log say about why the DB was marked suspect?--Gail ShawSQL Server MVP |
|
|
|
Rui Wang
Starting Member
2 Posts |
Posted - 2008-07-20 : 15:22:12
|
| Many thanks tkizer, I'll give it a try. Rui Wang |
|
|
|
mdubey
Posting Yak Master
133 Posts |
Posted - 2008-07-21 : 10:57:23
|
| 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.ManojMCP, MCTS |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-07-21 : 11:34:18
|
| 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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
|
|
Database 'msdb' cannot be opened