Database (Suspect)

Question

Database (Suspect)

vijju2k

SSCommitted

Points: 1531
More actions
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

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

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

Jeff W SSCertifiable Points: 5207 More actions · Answer 1

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

Ridok SSC Veteran Points: 290 More actions · Answer 2

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)

:.::.:.::

vijju2k SSCommitted Points: 1531 More actions · Answer 3

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

Wesley Brown SSChampion Points: 12045 More actions · Answer 4

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

vadba SSChampion Points: 11133 More actions · Answer 5

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

Amit Jethva SSCarpal Tunnel Points: 4707 More actions · Answer 6

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.

DimitrisAgelidis Ten Centuries Points: 1166 More actions · Answer 7

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

vijju2k SSCommitted Points: 1531 More actions · Answer 8

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

SQLBill SSC Guru Points: 51440 More actions · Answer 9

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