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