After You Execute sp_marksuspect

Once the procedure is created successfully, updates to the system catalog should be immediately disabled as follows:

1> sp_configure "allow updates", 0
2> go

Syntax

sp_marksuspect database_name

Example

1> sp_marksuspect PRODUCTION
2> go

Database 'PRODUCTION' has been marked suspect!

NOTE: You may now drop this database
via dbcc dbrepair (dbname, dropdb).

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 & 320 = 320) = 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|320
       WHERE name = @dbname
     IF @@error != 0 or @@rowcount != 1 
       ROLLBACK TRAN
     ELSE
       BEGIN
        COMMIT TRAN
        SELECT @msg = "Database '" + @dbname + "' has been marked suspect!"
        PRINT @msg
        PRINT " "
        SELECT @msg = "NOTE: You may now drop this database"
        SELECT @msg = @msg + "via dbcc dbrepair (dbname, dropdb)."
        PRINT @msg
        PRINT " "
      END