Atlanta Custom Software Development 

   Search        Code/Page
 

User Login
Email

Password

 

Forgot the Password?
Services
» Web Development
» Maintenance
» Data Integration/BI
» Information Management
Programming
  Database
Automation
OS/Networking
Graphics
Links
Tools
» Regular Expr Tester
» Free Tools

How to recover database from suspect mode?

Total Hit ( 20340)

Rate this article:     Poor     Excellent 

 Submit Your Question/Comment about this article

Rating


 


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
  1. Execute sp_resetstatus.
    syntax : sp_resetstatus database_name
  2. Use ALTER DATABASE to add a data file or log file to the database.
  3. Stop and restart SQL Server.
  4. 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.


Audit, Notify, Deploy and Manage SSIS
Download the Free 30-day Trial Version...Learn More About This Product...

Submitted By : Nayan Patel  (Member Since : 5/26/2004 12:23:06 PM)

Job Description : He is the moderator of this site and currently working as an independent consultant. He works with VB.net/ASP.net, SQL Server and other MS technologies. He is MCSD.net, MCDBA and MCSE. In his free time he likes to watch funny movies and doing oil painting.
View all (893) submissions by this author  (Birth Date : 7/14/1981 )


Home   |  Comment   |  Contact Us   |  Privacy Policy   |  Terms & Conditions   |  Blogs

© 2008 BinaryWorld LLC. All rights reserved.