Troubleshooting and Error Messages Guide

Home

How to Gather Information
About Read/Write Errors

How
to Reset a Database's suspect Status

Troubleshooting and Error Messages Guide

Chapter 2 Encyclopedia of Tasks

Other Useful Tasks

How to Mark a Database "suspect'"

The commands to create a procedure called sp_marksuspect, which turns on the suspect status bit on the specified database, are described in the stored procedure code below.

Use sp_marksuspect to prepare a damaged database that is to be dropped with dbcc dbrepair.

Warning!

The sp_marksuspect stored procedure is provided for your information-it is not supported at this time.

Before You Create and Execute sp_marksuspect

Before creating and executing sp_marksuspect, note the following:

Create sp_marksuspect in the master database.
Since this procedure modifies the system catalog, you must enable updates to the catalog before executing the procedure. Use the procedure below to enable updates:
```
1> use master 
2> go
```
```
1> sp_configure "allow updates", 1
2> go
```
If you change the name of the procedure, make sure the new procedure name begins with "sp_".
Review the Transact-SQL User's Guide explanation of how to create and execute stored procedures.

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


	Expand Search