Wednesday, March 2, 2011

How To Repair A SQL Server Database Marked As SUSPECT

If your Database unexpectedly "Database_Name" is marked as Suspected, then follow the below steps to bring the database to normal stage. 

Step 1.
EXEC sp_resetstatus 'AdventureWorks'
GO 

Step 2. Push the database to emergency mode from suspect.
ALTER DATABASE AdventureWorks SET EMERGENCY
GO 

Step 3. For checking the database errors.
DBCC checkdb('AdventureWorks')
GO 

Step 4. Push the database to a single user mode.
ALTER DATABASE AdventureWorks
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO 

Error Correction
DBCC CheckDB ('AdventureWorks',
REPAIR_ALLOW_DATA_LOSS)
GO 

Bring the database to Multi-user mode.
ALTER DATABASE AdventureWorks SET MULTI_USER
GO 

Rebuild the index 
Here is the complete script for rebuilding all the indexes in the database.
DECLARE @TableName VARCHAR(300)
DECLARE @sql NVARCHAR(550)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name
AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName +
'REBUILD WITH (FILLFACTOR = ' +
CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor
INTO @TableName
ENDCLOSE TableCursor
DEALLOCATE TableCursor
GO 

Finally Verify database status by running below query.
SELECT [Name], DBID, Status
FROM master.dbo.sysdatabases
GO
How to repair a Suspect Database in SQL Server. How to Restore SQL Server 2005 Suspect Database. How to repair a SQL Server 2008 R2 Suspect database. How to repair a SQL Server 2012 Suspect database. Suspect SQL Server 2012 Database. Recovering From A Suspect Database. Suspect SQL Server 2008 Database. Recovering a SQL Server Database from Suspect Mode. database corrupted. Sql suspect database repair Free Download. suspect database sql 2008. suspect database sql server 2008. how to repair suspect database in sql server 2000 repair suspect database sql 2008. how to recover suspect database in sql server 2008. what is suspect database in sql server. sql 2000 suspect database repair. suspect database in sql server 2000. SQL Server 2008 R2 (Suspect) mode. suspect database in sql server. Suspect Database Resetting the Suspect Status. How to recover msdb database from suspect mode. How to bring back a Database Online from Suspect mode. Help! My Database is Marked Suspect. How To Repair A Suspect Database In MSSQL. Attempt to Recover a Suspect Database.

1 comments:

One of my friend had same problem. He had tried above resolution methods but at end he was losing some data. He required his whole data because that was official data and every record in the table was very important. At last he had try Stellar Phoenix SQL Repair software to recover his data. It may helpful for your blog readers.

Post a Comment