You may face an issue occasionally which will hamper your work and keep you tensed.
I can name this issue as 'Suspect DB'.When the DB is marked as 'Suspect',then you cannot do anything with that database.
There are some possible reasons which mark the DB as Suspect,they are
1. Database could have been corrupted.
2. There is not enough space available for the SQL Server to recover the database during startup.
3. Database cannot be opened due to inaccessible files or insufficient memory or disk space.
4. Database files are being held by operating system, third party backup software etc.
5. Unexpected SQL Server Shutdown, Power failure or a Hardware failure.
You can get rid out of the suspect by applying the below solutions.
The best solution to recover suspect DB is :You can restore the latest backup set,but, what will happen when you dont have it?
Dont be panic!!!..Try my solution which should be chosen as Last option,when you are cornered
There are some pre-requisites which you need to abide before proceeding my steps:
1.This is only for Microsoft SQL Server
2.This may loss your data,while you are repairing with Allow data loss.
3.These statements will hit your DB directly,so.consult with SAP before proceeding this.
There are some steps which require to bring the suspected DB to Normal.
Step 1:Make the DB to work in Emergency mode which will block write access to it.
To bring it to emergency,the execute
ALTER DATABASE DBNAME SET EMERGENCY
GO
step 2:Run the another command to check for consistency errors and try to repair with 'Repair' Command.
ALTER DATABASE <DBNAME> SET SINGLE_USER
GO
DBCC CHECKDB ('DBNAME', REPAIR_ALLOW_DATA_LOSS)
GO
Step 3:If You find any consistency errors,then execute
USE <Your DB Name>
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 = 24
WHERE [Name] = 'YourDatabaseName'
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
Step 4:After executing the above command,then run the below command again,
ALTER DATABASE <DBNAME> SET SINGLE_USER
GO
DBCC CHECKDB ('DBNAME', REPAIR_ALLOW_DATA_LOSS)
GO
Step 5:When you find 0 consistency errors,you have to bring the DB from Emergency mode to normal.
ALTER DATABASE <DBNAME> SET MULTI_USER
GO
Refresh and check.
Revert me for any clarification.
