Troubleshoot Suspect Database Issue

Troubleshoot Suspect Database Issue

Problem

How to troubleshoot suspect database problem? How to bring it back online? What are the do you need to perform once the database is online?

Solution

Step 1: Bring the database online using below script
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 = 24
WHERE [Name] = ‘SuspectedDatabaseName’
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 2: Check for database corruption. This is very important step please execute it.

DBCC CHECKDB – Validate the overall database integrity
DBCC CHECKCATALOG – Validate the system catalog integrity
DBCC CHECKTABLE – Validate the integrity for a single table

Step 3: To resolve the corruption issue, please execute below commands

Drop and Recreate Index(es)
Move the recoverable data from an existing table to a new table

Update statistics
DBCC UPDATEUSAGE
sp_recompile

Step 4: Repeat Step 2 to validate all the corruption occurred