Post reply

Database (Suspect)

  • January 21, 2004 at 8:59 am

    #83913

    Good morning gurus,

         I being a novice in SQL Server 2000, I treid to reduce the SQL database log size by deleting both the transaction log files for the database. I did this after detaching the database.

         Once I deleted the transaction log files, when I tried to re-attach the database, I got an error saying "Device activation error....." for both the deleted Transaction log files. Realizing that I have messed up somewhere, I checked SQLServerCentral website and found Andy Warren's article "Attach and Detach...Again". Which mentions that SQL will give a problems when you try to re-attah the database after deleting transaction logs for databases with 2 or more t-logs. Alas, it was too late by then.

        I was able to successfully execute the "usp_AttachDBWithMissingLogFile" procedure detailed in Andy's article, but when I stopped and started my SQL server, it says (Suspect) besides the database name which I attached using the sp. Can any one please help me out with this problem.

    thank you all in advance,

    v

  • January 21, 2004 at 3:12 pm

    #491040

    I have a lot of databases out in the field that come in corrupted and have more than once had a 'suspect' database.   I don't have a solution for you.  Every situation I had seemed to be a little different.

    Make sure you make a copy of you datafiles if you haven't yet.  You want to be able to take more than one pass at trying to recover the database.

    If you haven't done this already, try using sp_attach_single_file_db.  Pay attention to any errors you get and try to do everything through sql statements (more errors are displayed).   After using the stored procedure are there any errors?  Why is the database suspect?  LSN, page allocation, etc..

    I wish I could be more help.  It may be helpful if you could report any errors in the SQL Server Error Logs, or from executing any commands

     


    "Keep Your Stick On the Ice" ..Red Green

  • January 21, 2004 at 3:14 pm

    #491041

    Hi, the solution in this link it's really good but isn't simple:

    http://www.spaceprogram.com/knowledge/sqlserver_recover_from_deleted_log.html

    Please, you must follow instructions step by step, reading carefully. It's work... Trust me.

    but, the 10 step may be don't you'll like specially if your database is very large.

    It was the best way what i could found.

     (i hope, that you can understand me, my english is poor, sorry)

    :.::.:.::

  • January 21, 2004 at 4:02 pm

    #491049

    Hi guys,

       thank you for your responses. I tried using sp_attach_single_file_db but it doesnot work. When I look into the SQL server logs, it says Device activation Error. Failed to open logfile.

      As a last ditch effor I will try following the steps listed in the above URL. If not I guess  I have to recreate the database

    thank you for all your help,

    v

  • January 22, 2004 at 5:50 am

    #491094

    You may want to look up how to set a database in emergency mode. If you can attach the database datafiles then set the database to emergency mode and bypass recovery you should be able to atleast copy the data out of the database to another location. I don't remember how to do it off the top of my head but we have had to do it a couple of times.

     

    Wes

  • January 22, 2004 at 6:53 am

    #491113

    Here's a link to the Microsoft KB article describing the procedure mentioned above:

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;165918

     

  • January 22, 2004 at 9:50 pm

    #491262

    to reduce the SQL database log size, you could have used enterprise manager or use TSQL commands like

    backup log  <DBNAME> with truncate_only  ( or to some backup device !! )

    dbcc shrinkfile (LOGFILENAME  , SIZE)

    you need not have delete the transaction log files for the database.

    http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20823435.html gives an idea of what are things you could do in such cases.

    - amit

     

     


    -- Amit


    "There is no 'patch' for stupidity."


    Download the Updated SQL Server 2005 Books Online.

  • January 23, 2004 at 6:41 am

    #491325

    Does the sql Admin user (the user that starts the sql service) have permission on the folder that you moved the files ?

  • January 23, 2004 at 7:59 am

    #491360

    I guess your are asking if a user have permission to access files stored in MSSQL\Data directory. If so, first I detached the database using Enterprise manager and then I was able delete the two t-log file.

    v

  • January 23, 2004 at 8:50 am

    #491395

    Have you checked out the BOL for information on Suspect Databases? Use the index tab and enter suspect database. There are several options, but one is to run sp_resetstatus. Check it out. -SQLBill

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply