Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2005
    Posts
    19

    Angry Unanswered: Transaction Log file deleted accidentaly

    Hi,
    I have deleted the transaction log device file of one of my databases. Now my database is in a suspect mode. How do i resolve this issue. Plllllllls help

  2. #2
    Join Date
    Aug 2004
    Posts
    38
    1. Drop the database using dbcc dbrepair
    2. Recreate the database - same size as before.
    3. Load the new database from the lastest full dump.
    4. Roll forward all transaction log dumps up until the point of failure.

    Probably not the reply that you were looking for, but your database cannot be safely recovered. It is possible to force it online and you would be able to go in and look at some of the data (and maybe even bcp some of it out), but there will be some degree of corruption in there. How bad it is will depend on how busy the database was when the transaction log was deleted. You won't be able to recover the database in any meaningful way though.
    Last edited by KevR; 10-18-05 at 16:52.

  3. #3
    Join Date
    Feb 2005
    Posts
    19
    I dont have any backups of the database . Can i take the dump of the database when the database is in suspect model.

  4. #4
    Join Date
    Aug 2004
    Posts
    38
    I wouldn't have thought it possible to dump a database that's missing its transaction log. With no backup, probably the best you could expect is to bcp out whatever you can recover.

    Allow updates in the server and set the status in sysdatabases to -32768 for this database only. Reboot the server. This should bypass recovery, which will allow you to use the database even though it hasn't undergone recovery. You can probably query sysobjects at this point to get a table list if you don't have one. You might also be able to run sp_helps and sp_helptexts to extract your schema if you don't have it stored elsewhere.

    You should now be able to connect externally using bcp and take out some of the data. Some bcps are likely to hang/crash, but you never know, you might be lucky.

    I can't see there's much else you can do. Unless anyone else has any suggestions.....

  5. #5
    Join Date
    Oct 2004
    Posts
    91

    Log file

    Hi -

    If the problem is still a problem...

    try to

    http://sybooks.sybase.com/onlinebook...11704;pt=11528


    best of luck

  6. #6
    Join Date
    Mar 2006
    Posts
    25

    Unhappy

    for this problem u need to update ur sysdatabases table

    use master
    sp_configure "allow updates to system tables",1
    update sysdatabases
    set status=status-320
    where name="db_name" and status=status&320
    sp_configure "allow updates to system tables",0


    Quote Originally Posted by amber_sawant
    Hi,
    I have deleted the transaction log device file of one of my databases. Now my database is in a suspect mode. How do i resolve this issue. Plllllllls help
    Last edited by prashant_add; 03-27-06 at 06:14.

  7. #7
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    1. touch a new file with the same permissions as the original tx log file.
    2. update sysdevices with the entry of the tx log file.
    3. have the db in by-pass recovery -32768
    4. recycle the server
    5. dbcc rebuild_log(<dbid>,1,1)
    6. change status in sysdatabases to 0
    7. recycle

Posting Permissions

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