suspect database

Are you having any luck with this?

If you reject this answer, I'll give the correct one in a few minutes :-)

rchambel,

If you have something to say, why don't you just say it?

If it has merit, you'll get the points.

For SQL Server 6.5, my answer is correct.

:-)
I'm not here for the points... if you want to give them to someone else, be my guest.

So here it goes:
This is a little bit tricky but it works on every database you might have:

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

The sp_resetstatus procedure can be executed only by the system administrator.
ALWAYS SHUT DOWN SQL Server IMMEDIATELY AFTER executing this procedure.

Syntax: sp_resetstatus database_name
Example: sp_resetstatus PRODUCTION
 
Database 'PRODUCTION' status reset!
WARNING: You must reboot SQL Server prior to
         accessing this database!

Stored Procedure Code:


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


After the procedure is created, immediately disable updates to the system tables:

sp_configure 'allow updates',0
go
 
reconfigure with override
go

Ok, that's all.

From this point forward, you may reset the suspect status on any database by calling sp_resetstatus "db_name"

I don't see anything in your post that makes my answer wrong.  Most of what you suggested is either syntactically or conceptually the same.

You're right... :-(

Both the answers are perfectlu correct but surely the most important thing is finding why the database is suspect and the repairing that. One particluarly nasty reason for the database to become suspect is the deletion of a log file!

I would advise that the advise previously given of looking through the error log followed and action taken to prevent the problem occuring again.

SQL Server is very weak on its implementation. I've worked with it for several years and every now and then it corrupts it's own data!

Sometimes this is due to system abnormal shutdown and the SQL Server recover mechanism is not the best I've seen.

I'm sorry to say that but since I've changed to Oracle everything turned out different. It's more stable, it's recovering works flawlessly, it's fast, the PL/SQL language is easier to understand and code...

.... and I do not intend to get back to SQL Server for the next years.

best regards.