August 18, 2009 at 9:45 am
I have the following error:
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. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information. (Microsoft SQL Server, Error: 926)
------------------------------------------
I don´t have a backup file for MSDB database. What can I do?
Thanks a lot.
Viky.
August 18, 2009 at 10:10 am
First step....Have a look through the logs to see if there is there any indication of why the database was marked as suspect.
Actually, I came across this posting a while back. It's worth you taking a read through this.
August 18, 2009 at 12:46 pm
First thing we need to see is the SQL error logs. There should be a message in there indicating why MSDB is suspect. Hopefully, it'll be something easy to resolve.
If it's not then, because you have no backups (why not?) you'll have to rebuild MSDB. There are instructions somewhere on MSDN. Your favourite search engine should be able to find them.
Note that you will lose all your jobs and maintenance plans, backup and job history, SQL Agent alerts, any SSIS or DTS packages saved in the server and maybe some other things. I can't remember everything that's stored in MSDB.
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
August 23, 2009 at 9:49 pm
There might be couple of reasons of database being in suspect mode. Check that the log files and data files have enough space to grow, if space is less, make some storage space and restart the service.
If this does not help reset the database and restart the server,
Sp_configure 'allow updates', 1
Reconfigure with override
GO
sp_resetstatus 'mydbname'
GO
If this does not help then last attempt would to put database in emergency mode and try to rebuild the log file if log is corrupted or you can pull out data from tables, since in emergency mode you can read the tables.
update master..sysdatabases set status = status | -32768
where name = 'db'
Make sure to run the following script to disable the updates to system database table.
Sp_configure 'allow updates', 0
Reconfigure with override
PS: I have fixed suspected database issue using this approach on user databases, but never got opportunity on system databases.
Wish you luck..
Swarndeep
http://talksql.blogspot.com
August 23, 2009 at 10:26 pm
Do the other things above before you resort to this:
http://rip747.wordpress.com/2008/05/26/rebulding-msdb-in-sql-server-2005/
CEWII
August 23, 2009 at 11:52 pm
since1980 (8/23/2009)
Sp_configure 'allow updates', 1Reconfigure with override
GO
sp_resetstatus 'mydbname'
GO
...
update master..sysdatabases set status = status | -32768
where name = 'db'
...
Sp_configure 'allow updates', 0
Reconfigure with override
This advice is only valid for SQL 2000 databases. Since the OP has a SQL 2005 database and this is the 2005 forum, this information is not usable as allow_updates does not work on SQL 2005 and sysdatabases is no longer a table.
On SQL 2005 there is a documented way to set a DB into emergency mode, ALTER DATABASE ... SET EMERGENCY
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
August 24, 2009 at 2:23 pm
Thanks. Sounds good.
Swarndeep
http://talksql.blogspot.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply