DELETE .ldf

Question

DELETE .ldf

Cláudia Sousa Rego

SSCommitted

Points: 1780
More actions
July 6, 2006 at 4:25 pm

#173307

I'm really a butter head
I have done the most stupid thing: i delete my transaction log from file system.
I had a DB named cars, soo a have in file system cars_data.mdf and cars_log.ldf.
This DB has being replicated and this process filled up the transaction log.
so i cannot do anything!! I tried disable replication, i tried delete table, shrink, etc but all gives the same message: "transaction log is full".
the samrtest thing: i stopped sql agent and DELETE!
And now i dont know what to do...
the only thing that occurs me has installing again sql server!! HELP
Cláudia - the butter head
Regards,
Cláudia Rego
www.footballbesttips.com

Viewing 8 posts - 1 through 7 (of 7 total)

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

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

I have no idea whether this would work, but you might try copying the data file to another server and attach it using the attach_rebuild_log option. The you could back it up and restore it over the old one. I can think of a lot of reasons why this might not work, but if you don't have a backup....

And then again, I might be wrong ...
David Webb

laker_42 SSCarpal Tunnel Points: 4785 More actions · Answer 2

Hi Claudia,

Did you try a transaction log backup before deleting the file? That should have cleared out the log file for you.

Anyways, have you tried using the following command to rebuild the log file:

DBCC REBUILD_LOG('dbname','log file path')

Here is one article on it: http://www.sqlmag.com/Articles/Index.cfm?ArticleID=26044&DisplayTab=Article

You can google this for some more info on it.

I take it that you don't have a recent backup of this database? Could you try copying the log file of the replicated database? Obviously you are going to corrupt something but at this point you pretty much will have to do some repair of some kind no matter what you try.

No matter what you try, I would make a copy of the mdf file before trying anything else. So you know no matter what happens, you can get back to where you are now. No need to make the situation even worse.

HTH,

John

Cláudia Sousa Rego SSCommitted Points: 1780 More actions · Answer 3

I solved!!!!

I tried what you said, John. I used DBCC REBUILDLOG, but didn't solved..

I tried 10000 stored procedures, but didn't worked....

I solved using this:

Open master, and update the status (to clear emergency mode) and the category field (to clear replication).

Know i need feedback..

This is a good solution or once again i made a stupid thing?'

Thanks

Cláudia

Regards,

Cláudia Rego

www.footballbesttips.com

Wesley Brown SSChampion Points: 12045 More actions · Answer 4

there is a real possibility that you may have lost data. if there were transactions in the log that had not been commited to the datafile they are now gone. if this database is the recepent of replication no big deal reinit and you should be back to normal.

Wes

Cláudia Sousa Rego SSCommitted Points: 1780 More actions · Answer 5

Loosing data wasn't a problem. I can get all data again with restart of replication.

Claúdia

Regards,

Cláudia Rego

www.footballbesttips.com

Clive Strong SSChampion Points: 11222 More actions · Answer 6

If you get transaction log is full, you should manually run the scheduled job or just write some code on QA to back the log up and allow space for further transactions.

Another method, although not recommended, but you can do this if you can lose the transacions (I have only ever done this in development) is to change the recovery model to simple and then back to full. I repeat, this is not something I would advise on a production environment!

Razvan-Mihai Dudu SSC Enthusiast Points: 161 More actions · Answer 7

At this moment the best advice is to buy more storage (hard drives, RAID preffered) and to add log file(s) to your database (if it is not very large, stick to the primary file group).

Having two or more physical files for the transaction log in two (or more partitions / disks / RAID arrays) helps avoiding "transaction log is full". Having them spreaded on multiple physical disks / RAID arrays might improve speed...

Anyway, a solid backup strategy should be in place, even if you can recover lost data from another server via replication...

The solution mentioned by Clive is working (whith an addendum: you can backup your transaction log and THEN switch between recovery models), but... he's right: it is to be avoided in production environment.

To learn is not to know.