Resetting the Suspect Status

Microsoft SQL Server returns error 1105 and sets the status column of sysdatabases to suspect if SQL Server is unable to complete recovery on a database because the disk drive no longer has any free space. Follow these steps to resolve the problem:

  1. Execute sp_resetstatus.
  2. Use ALTER DATABASE to add a data file or log file to the database.
  3. Stop and restart SQL Server.

    With the extra space provided by the new data file or log file, SQL Server should be able to complete recovery of the database.

  4. Free disk space and rerun recovery.

sp_resetstatus, 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. Otherwise, you might damage your database. For more information, see SQL Server Books Online.

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

  

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

sp_resetstatus database_name

This example turns off the suspect flag on the PRODUCTION database.

sp_resetstatus PRODUCTION

  

Here is the result set:

Database 'PRODUCTION' status reset!

WARNING: You must reboot SQL Server prior to accessing this database!

  

sp_resetstatus Stored Procedure Code

Here is the code of the sp_resetstatus stored procedure:

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