A database will be marked suspect for one of the following reasons:
- If one or more database files are not available.
- If the entire disk where database files are residing is not available.
- If one or more database files are corrupted.
- If a database resource is being held by the operating system.
We can try any of the suitable below resolution steps to bring back the DB online.
Method:1
USE Master
GO
GO
– Determine the original database status
SELECT [Name], DBID, Status
FROM master.dbo.sysdatabases
GO
SELECT [Name], DBID, Status
FROM master.dbo.sysdatabases
GO
– Enable system changes
sp_configure ‘allow updates’,1
GO
RECONFIGURE WITH OVERRIDE
GO
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
UPDATE master.dbo.sysdatabases
SET Status = 24
WHERE [Name] = ‘YourDatabaseName’
GO
– Disable system changes
sp_configure ‘allow updates’,0
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure ‘allow updates’,0
GO
RECONFIGURE WITH OVERRIDE
GO
– Determine the final database status
SELECT [Name], DBID, Status
FROM master.dbo.sysdatabases
GO
SELECT [Name], DBID, Status
FROM master.dbo.sysdatabases
GO
Method:2
EXEC sp_resetstatus ‘yourDBname’;
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb(‘yourDBname’)
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (‘yourDBname’, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER
Method:3
use master
sp_detach_db ‘mydb’
use master
sp_attach_db ‘mydb’,'E:\Sqldata\mydbdata.mdf’,'E:\Sqldata\mydblog.ldf’
In this method, we can detach the Db from the server either by using the above command or through GUI. After detaching the DB we need to attach.
While attaching if the log file of the DB is very huge and inaccessible it will throw errors and will not allow us to attach it. In such don’t select the log file only select the ‘.mdf’ file and SQL server will assume that there is no log for the Db and it will create/rebuild a new log.
Method:4
Check if any of the SQL Jobs are accessing the DB this will cause the log to grow and even stops us from detach/attach of DB. Find the SPID and kill it if its not critical then it might allow to proceed accordingly.
After bringing back the DB online by using any method above always run below DBCC commands to make sure everything is back to normal.
DBCC CHECKDB – Validate the overall database integrity
DBCC CHECKCATALOG – Validate the system catalog integrity
DBCC CHECKTABLE – Validate the integrity for a single table
Useful Links:
No comments:
Post a Comment