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
