Resetting the Suspect Status

The sp_resetstatus procedure shown below turns off the suspect flag on a database but leaves all other database options intact.

Caution Use sp_resetstatus only when directed by your primary support provider or this manual. Otherwise, you might damage your database.

Because this procedure modifies the system tables, the system administrator must enable updates to the system tables before creating this procedure. To enable updates, use this procedure.

use master
go
  
sp_configure 'allow updates',1
go
reconfigure with override
go
  

After the procedure is created, immediately disable updates to the system tables.

sp_configure 'allow updates',0
go
  
reconfigure with override
go
  

The sp_resetstatus procedure can be executed only by the system administrator. Always shut down SQL Server immediately after executing this procedure.

Syntax

sp_resetstatus database_name

Example

sp_resetstatus PRODUCTION
  
Database 'PRODUCTION' status reset!
WARNING: You must reboot SQL Server prior to
         accessing this database!
  

Stored Procedure Code

CREATE PROC sp_resetstatus @dbname varchar(30) AS
DECLARE @msg varchar(80)
IF @@trancount > 0
        BEGIN
            PRINT "Can't run sp_resetstatus from within a transaction."
            RETURN (1)
        END
IF suser_id() != 1
        BEGIN
            SELECT @msg =  "You must be the System Administrator (SA)"
            SELECT @msg = @msg + " to execute this procedure."
            RETURN (1)
        END
IF (SELECT COUNT(*) FROM master..sysdatabases
            WHERE name = @dbname) != 1
        BEGIN
            SELECT @msg = "Database '" + @dbname + "' does not exist!"
            PRINT @msg
            RETURN (1)
        END
IF (SELECT COUNT(*) FROM master..sysdatabases
            WHERE name = @dbname AND status & 256 = 256) != 1
        BEGIN
            PRINT "sp_resetstatus can only be run on suspect databases."
            RETURN (1)
        END
BEGIN TRAN
        UPDATE master..sysdatabases SET status = status ^ 256
            WHERE name = @dbname
        IF @@error != 0 OR @@rowcount != 1
            ROLLBACK TRAN
        ELSE 
            BEGIN
                COMMIT TRAN
                SELECT @msg = "Database '" + @dbname + "' status reset!"
                PRINT @msg
                PRINT " " 
                PRINT "WARNING: You must reboot SQL Server prior to  "
                PRINT "         accessing this database!"
                PRINT " "
            END
  
go