Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2003
    Posts
    18

    Unanswered: database marked suspect

    Hi Guys,
    I am in a terrible fix. While loading my tran logs on to a database which was offline, the load tran exited with error stating that the index sort failed. So, in order to remove the cached page due to which the index creation in the load transaction failed, I rebooted the server(silly of me) and the database while the reboot was on could not be recovered. Now, I cannot drop the database and it cannot be recovered. Please help.

    Regards

    DB

  2. #2
    Join Date
    Feb 2003
    Location
    Brisbane, Australia
    Posts
    110

    Re: database marked suspect

    You could try updating the status of the database in the sysdatabases table in master.. Not really advisable but if all you want to do is drop the database this may work. Have a read through some sybase manuals they may tell you more about the status field.

    Hope this helps... good luck

  3. #3
    Join Date
    Dec 2002
    Posts
    104

    Re: database marked suspect

    Originally posted by dba_sybase2003
    Hi Guys,
    I am in a terrible fix. While loading my tran logs on to a database which was offline, the load tran exited with error stating that the index sort failed. So, in order to remove the cached page due to which the index creation in the load transaction failed, I rebooted the server(silly of me) and the database while the reboot was on could not be recovered. Now, I cannot drop the database and it cannot be recovered. Please help.

    Regards

    DB
    hello,

    can u drop ur database using dbcc dbrepair as ur database must be marked as suspect ,so dbcc dbrepair is used to drop database marked as suspect.if it is not marked as suspect then u can explicitly mark it suspect by using sp_marksuspect procedure..if not available the create it using the code given in sybase documenatation.
    Secondly, u can start ur server in bypass recovery mode for that database...by changing the status of suspected database to -32768 in sysdatabases.then reboot server it will start in bypass recovery mode.

    hope this will help u,
    Pooja
    Last edited by pooja; 07-15-03 at 07:30.

  4. #4
    Join Date
    Feb 2004
    Posts
    9

    Angry Suspected Database

    The commands to create a procedure called sp_resetstatus, which turns off the "suspect" flag on a database while leaving all other database options intact, are shown on . This is the safest method. An alternative approach using Transact-SQL commands is also presented. Reset a database's "suspect" status only when instructed in this manual or by Sybase Technical Support. Otherwise, you may damage your database.

    Before creating and executing sp_resetstatus, note the following:
    1.Create sp_resetstatus in the master database.
    2.You must have sa_role to execute this procedure.
    3.Since this procedure modifies the system catalog, you must enable updates to the catalog before executing the procedure. Use the procedure below to enable updates:

    1> use master
    2> go
    1> sp_configure "allow updates", 1
    2> go


    After successfully executing this procedure, you must do two things:

    1.Immediately shut down SQL Server.
    2.Restart SQL Server and immediately disable updates to the system catalog as follows:
    1> sp_configure "allow updates", 0
    2> go
    Syntax
    sp_resetstatus database_name
    Example
    1> sp_resetstatus PRODUCTION
    2> go

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

    -----------------------------------------------------------------
    Code to create procedure -- Make sure to create with SA
    -----------------------------------------------------------------
    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."
    PRINT @msg
    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 may only be run on suspect databases."
    RETURN (1)
    END
    BEGIN TRAN
    UPDATE master..sysdatabases SET status = status - 320
    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


    After reseting the server - it will try to allocate/setup log pages - if its not successful than try to load the database again.

    If not, you will need to drop the database

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •