Binaryworld - How to recover database from suspect mode? ... [ SQL Server -> Server Operations ]

Sometime your database may go into suspect mode. A database can be marked suspect for one of the following reasons (this is from SQL Server Books Online):

If one or more database files are not available. 
If the entire database is not available. 
If one or more database files are corrupted. 
If a database resource is being held by the operating system.

Before you do any changes with system table run the following command to enable adhoc changes to the system tables

Click here to copy the following block

sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO

For SQL 6.5 and below

Click here to copy the following block

UPDATE master..sysdatabases SET status=-32768 WHERE name='<dbname>'

For SQL 7.0

Click here to copy the following block

UPDATE master..sysdatabases SET status=32768 WHERE name='<dbname>'

For SQL 2000
Execute the following script to create sp_resetstatus sp

Click here to copy the following block

IF EXISTS ( SELECT * from sysobjects where name = 'sp_resetstatus' )
DROP PROCEDURE sp_resetstatus
GO

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

Now perform the following steps

Execute sp_resetstatus.
syntax : sp_resetstatus database_name
Use ALTER DATABASE to add a data file or log file to the database.
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.

Free disk space and rerun recovery.