how to recover a suspect database

how to recover a suspect database

Post by junsoftwar » Sun, 06 Jan 2002 13:19:20



SOS!
   one of my MS SQL database is marked by "suspect
database".
How I can recove it?
   I have tried to recover it by "DBCC DBRECOVER
mydatabase". however, it return error 3624. and I do not
know what means it is.
   I have tried do it by "Reset the suspect status by
executing sp_resetstatus" and changed to mark, but,
mydatabase is not open.
   Please help me. Thanks.
 
 
 

how to recover a suspect database

Post by Dan Guzma » Sun, 06 Jan 2002 13:31:15


Check the SQL Server error log.  There should be messages indicating the
reason the database was marked suspect (e.g. missing files, permissions).
Execute sp_resetstatus only after the problem has been corrected.

See sp_resetstatus in the Books Online for details.

Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy  Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------


Quote:> SOS!
>    one of my MS SQL database is marked by "suspect
> database".
> How I can recove it?
>    I have tried to recover it by "DBCC DBRECOVER
> mydatabase". however, it return error 3624. and I do not
> know what means it is.
>    I have tried do it by "Reset the suspect status by
> executing sp_resetstatus" and changed to mark, but,
> mydatabase is not open.
>    Please help me. Thanks.


 
 
 

how to recover a suspect database

Post by junsoftwar » Sun, 06 Jan 2002 21:55:15


Dear Sir:
Thanks your kind help very much.
I have checked my error log. And I attached the log.
Whould you mind teach me how to recover my SUSPECT
DATABASE?

  errorlog.txt
2K Download
 
 
 

how to recover a suspect database

Post by Dan Guzma » Mon, 07 Jan 2002 04:26:04


Did you restart SQL Server after executing sp_resetstatus as instructed in the
Books Online?  If so, I suggest you contact MS PSS or restore from your last
known good backup.

The error log shows a stack dump during recovery.  This can be an indication
of database corruption, hardware problem or other issue.  MS PSS can help
determine the specific cause of the problem (e.g. corrupted log) and provide a
resolution.

You can put the database in bypass recovery mode in using the script below in
order to perform diagnostics or extract data.  However, because recovery
hasn't occurred, data will be inconsistent and the database will still be
suspect.  See
http://support.microsoft.com/directory/article.asp?ID=KB;EN-US;Q165918 for
more information.

USE master
EXEC sp_configure 'allow',1
RECONFIGURE WITH OVERRIDE
GO

--turn on bypass recovery
UPDATE sysdatabases
SET status = status ^ 32768
WHERE name = 'Ver32DB'
EXEC sp_configure 'allow',0
RECONFIGURE WITH OVERRIDE
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy  Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------


Quote:> Dear Sir:
> Thanks your kind help very much.
> I have checked my error log. And I attached the log.
> Whould you mind teach me how to recover my SUSPECT
> DATABASE?

 
 
 

how to recover a suspect database

Post by px [M » Wed, 09 Jan 2002 19:01:47


When a database has been marked suspect during recovery, putting the database
into bypass mode may allow you to USE the database. Bypass mode allows access to
the database. Although modifications cannot be permitted, BCP OUT and SELECT
queries can be performed.

If the database has been marked suspect due to the transaction log
filling up, the system administrator (SA) can run DUMP TRANSACTION WITH NO_LOG
on the suspect database.

ONLY the SA may perform the following operations:

1. Enable ad hoc updates to system tables:

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

2. Put the database in bypass mode:

   a. Issue the following query:

            USE master
            GO

            BEGIN TRANSACTION
            GO

            UPDATE sysdatabases
            SET status =  status | 0x8000
            WHERE name = 'dbname'
            GO

      (Note: dbname is the name of the suspect database.)

   b. Check to make sure only ONE row was affected by the previous statement. If
      not, issue a ROLLBACK TRANSACTION and start over with the BEGIN
      TRANSACTION statement. Otherwise, issue a COMMIT TRANSACTION and continue.

3. Take the database out of bypass mode:

   a. Issue the following query:

            USE master
            GO

            BEGIN TRANSACTION
            GO

            UPDATE sysdatabases
            SET status = status ^ 0x8000
            WHERE status & 0x8000 = 0x8000 AND name = 'dbname'
            GO

   b. Check to make sure only ONE row was affected by the previous statement. If
      not, issue a ROLLBACK TRANSACTION and start over with the BEGIN
      TRANSACTION statement. Otherwise, issue a COMMIT TRANSACTION and continue.

4. Shut down and restart SQL Server.

NOTE: The data type of the sysdatabases..status column changed from a smallint (2
bytes) in SQL Server 6.5 and earlier versions to an int (4 bytes) in SQL Server
7.0 and later. Since these data types are signed, 0x8000 in the status column is
a negative number in SQL Server 6.5 and a positive number in SQL Server 7.0. If
you use decimal numbers in the update statements in steps 2 and 3 instead of the
hexadecimal 0x8000 as shown, you must use -32768 in SQL Server 6.5 and earlier
and 32768 in SQL Server 7.0 and later.

****************************************************************************
This posting is provided "AS IS" with no warranties, and confers no rights.
****************************************************************************

Thanks,
Peter

 
 
 

1. How to recover the SUSPECTED database

Hello all:

I have problem, my databases are marked as suspect, how do i recover
that. If i try to attach it back. I am not able to do so.

Please anybody out there help me out in this regard.

Thanks in advance.
shankar.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

2. OODBMS vs RDBMS

3. view and recover a suspected database

4. Few Questions

5. How to recover the SUSPECTED database

6. Cluster problem

7. 24247-OH-CINCINNATI-Visual C++-Windows NT-SYBASE-ORACLE-AIX-Object Oriented Deve

8. Suspect database, please help me recover!

9. How to recover Database marked as Suspect ?

10. Recover Suspect Database - Lost Transaction Log

11. Recovering a suspect database

12. how to recover databases from suspect mode????