After You Execute sp_resetstatus

After successfully executing this procedure, you must do two things:

Immediately shut down Adaptive Server.
Restart Adaptive Server and immediately disable updates to the system catalog as follows:
```
1> sp_configure "allow updates", 0
2> go
```

Syntax

sp_resetstatus database_name

Example

1> sp_resetstatus PRODUCTION
2> go

Database 'PRODUCTION' status reset!

WARNING: You must reboot Adaptive 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."
     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
      PRINT "sp_resetstatus may only be run on suspect databases."
      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 + "' status reset!"
         PRINT @msg
         PRINT " " 
         PRINT "WARNING: You must reboot Adaptive Server prior to  "
         PRINT "          accessing this database!"
         PRINT " "
       END

The status adjustment by 320 reflects the use of 256 to mark the database suspect and an additional 64 to indicate that it was in recovery when it was marked suspect.