SQL Server Performance

SUSPECT DATABASE URGENT!!!

Discussion in 'General Developer Questions' started by cemuney, Dec 2, 2005.

  1. cemuney New Member

  2. FrankKalis Moderator

  3. cemuney New Member



    ok sorry
    i dont want disturb anyone....

    it was just 187KB in zipped format.

    i just wanted to get help, if anyone wants..

    You know that,
    There are lots of ways that data becomes corrupted and Suspect.
    I thougth that some one can help to understand the kind of problem.
    Cause u know that Suspect db issue is not a standard problem.

    Anyway, You can delete this topic!!!

    Thanks everyone
  4. FrankKalis Moderator

    I didn't want to sound harsh...[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  5. cemuney New Member

    <br />ok nop <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  6. Luis Martin Moderator

    That is the error when I try to download your database:

    HTTP1.1 STATUS 403 Remote Access to this object forbidden This file cannot be directly accessed from a remote site, but must be linked through the Brinkster Member's site.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  7. cemuney New Member


    You must copy the link and paste it to address in explorer...
    or Download accelerator
    thanks
  8. Luis Martin Moderator

    Cemuney:

    When I try to attach database I have and error: Not valid database file.
    Did you dettach database before rar?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  9. ghemant Moderator

    Hi,<br />please refer * How to recover user databases from a “Suspect” status * By Svetlana Kuvshinkova on MyITForum .and search relevant thread in this forum its already discussed earlier also.<br /><br /><img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />Regards<br /><br />Hemantgiri S. Goswami<br />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami<br />
  10. cemuney New Member

    <br />Thanks Luis Martin<br /><br />but i have only that files. After power problem the database was suspect<br />i stop the SqlServer and copy the mdf and ldf files..<br /><br />i tried<a target="_blank" href=http://tinyurl.com/2ubq2>http://tinyurl.com/2ubq2</a> procedures but no solution.<br /><br />i got (invalid file header) error. i think the only option is to find a hex editor to find the currupted part of the file but itis not easy<img src='/community/emoticons/emotion-6.gif' alt=':(' /><br /><br /><br />i think database died...<br /><br />althought<br />Thank you very much LuisMArtin, and also frank and ghemant...<br />
  11. Luis Martin Moderator

    I'm afraid you are waisting time with editor.
    Try the following:

    Resetting the Suspect Status
    Microsoft® SQL Server™ 2000 returns error 1105 and sets the status column of sysdatabases to suspect if SQL Server is unable to complete recovery on a database because the disk drive no longer has any free space. Follow these steps to resolve the problem:

    Execute sp_resetstatus.


    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.
    sp_resetstatus turns off the suspect flag on a database, but leaves all other database options intact.



    Caution Use sp_resetstatus only when directed by your primary support provider or as recommended in Troubleshooting. Otherwise, you might damage your database.


    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

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

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

    sp_resetstatus can be executed only by the system administrator. Always shut down SQL Server immediately after executing this procedure.

    The syntax is:

    sp_resetstatus database_name

    This example turns off the suspect flag on the PRODUCTION database.

    sp_resetstatus PRODUCTION

    Here is the result set:

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

    sp_resetstatus Stored Procedure Code
    Here is the code of the sp_resetstatus stored procedure:

    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



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  12. satya Moderator

    Also you need to investigate what causes this database into SUSPECT, check event viewer and SQL error logs for more information.

    You need to confirm the steps execution referred above by Luis as per BOL.

    We all are here to help you out, ifyou can be specific with you question it will be more easy for others to provide the solution. It is not recommended to give such links to download too.

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  13. cemuney New Member

    <br />Thanks all <img src='/community/emoticons/emotion-1.gif' alt=':)' />)<br /><br />i have already tried your suggestion LuisMartin....<br /><br />then i got the error...<br /> <br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />The header for file 'D:program Filessql7Datagemlik_data.mdf' is not a valid database file header. The PageAudit property is incorrect.<br /></font id="code"></pre id="code"><br /><br />as i mentioned before i already tried.<br /<a target="_blank" href=http://tinyurl.com/2ubq2>http://tinyurl.com/2ubq2</a> procedures but no solution <img src='/community/emoticons/emotion-6.gif' alt=':(' />(<br /><br /><br />Anyway thanks 4 all again...<br />Have a nice week!!!
  14. satya Moderator

  15. satya Moderator

    Cem
    If you have the similar problem after the resolution applied that are reffered above, then please explain. If you edit your post it will not be clear enough to understand the issues.

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  16. cemuney New Member


    Satya,
    i just delete my database link according to advices.
    I tried all methods and I couldnt recover my database.
    Topic can be closed.
    Anyway thanks all again.

Share This Page