Marking a Database Suspect

The sp_marksuspect procedure shown below turns on the suspect status bit on a particular database. Use this procedure to prepare a damaged database so that it can be dropped using DBCC DBREPAIR.

Because this procedure modifies system tables, the system administrator must enable updates to the system tables before creating the 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
  

Syntax

sp_marksuspect database_name

Example

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

Stored Procedure Code

CREATE PROC sp_marksuspect @dbname varchar(30) AS
    DECLARE @msg     varchar(80)
    IF @@trancount > 0
        BEGIN
            PRINT "Can't run sp_marksuspect 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."
            PRINT @msg
             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
                SELECT @msg = "Database '" + @dbname + "' "
                SELECT @msg = @msg + "is already marked suspect."
                PRINT @msg
                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 + "' has been marked "                SELECT @msg = @msg + "suspect!"
            PRINT @msg
            PRINT " "
            SELECT @msg = "WARNING: This database should now be "
            SELECT @msg = @msg + "dropped via DBCC DBREPAIR."
            PRINT @msg
            PRINT " "
        END
go