Search

Friday, January 20, 2012

Database 'msdb' cannot be opened.


Today I am getting the below error while querying Database:
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 searched the net and found the below solution: 
In SQL Server 2005 & Above
steps need to be run and taken if DB is in Suspect Mode.


EXEC sp_resetstatus 'Database_Name';
ALTER DATABASE Database_Name SET EMERGENCY
DBCC checkdb('Database_Name')
ALTER DATABASE Database_Name SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('Database_Name', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE Database_Name SET MULTI_USER


In SQL Server 2000
USE Master
GO
-- Determine the original database status
SELECT [Name], DBID, Status FROM master.dbo.sysdatabases
GO
-- Enable system changes
sp_configure 'allow updates',1
GO
RECONFIGURE WITH OVERRIDE
GO
-- Update the database status
UPDATE master.dbo.sysdatabases
SET Status = 8 or 32768 --8 means online & 32768 means emergency
WHERE [Name] = 'Database_Name'
GO
-- Disable system changes
sp_configure 'allow updates',0
GO
RECONFIGURE WITH OVERRIDE
GO
-- Determine the final database status
SELECT [Name], DBID, Status
FROM master.dbo.sysdatabases
GO

4 comments:

  1. For repair sql files exist recovery for sql server. It utilizes high end ways of repairing sql data. The program can't modify your source data during restoration. It starts under low system requirements.

    ReplyDelete
  2. this cannot be done for msdb . this is the message it get when i run the query
    "Msg 5058, Level 16, State 6, Line 2 Option 'EMERGENCY' cannot be set in database 'msdb'."


    Safdar

    ReplyDelete
  3. Thank you Arun..this really helped me out....thank you very much...

    ReplyDelete
  4. Sometimes due to overstorage of database files the memory management gets disturbed as well as allocation as a result generate Error 926 database can not be open types so in this typical condition and to resolve it you must go through this post which helps you to how to eradicate it must visit:- http://www.sqlrecoverysoftware.net/blog/sql-error-926-and-945.html

    ReplyDelete