Transaction log lost... now what?

[monkle]

Our transaction log became too full, to the point that our database could not be accessed. One of my co-workers decided to deal with the problem by manually deleting the transaction log off the server.

Is there any way we can repair the functionality of the database without losing the data in the database? There was quite a large amount done in the database yesterday, and it would really hurt to have to re-do an entire day's work.

We have tried un-delete utilities, and our transaction log has been overwritten in memory during our attempts to restore it.

I have never had to handle this particular scenario before. Is there anything we can do? I've been looking around online, but haven't found anything that has helped.

 

[SQLBill]

You say the tlog was deleted, but you don't tell us what problems you are having. What error messages are you seeing? Was the database detached before the file was deleted?

-SQLBill

Posting advice: FAQ481-4875

 

[monkle]

The problem has been resolved. Sorry for a useless post.

 

[theresatan]

I have the same problem, How to solve it?

Thanks!

TT

 

[mrdenny]

If the database has been detached you can use sp_attach_single_file_db to attach an mdf file which doesn't have an ldf file. It should create a new ldf and allow you to attach the database.

Please post the error you are getting so we know better how to direct you.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[theresatan]

The database did not detached, but log file was lost.

The command is:
EXEC sp_attach_single_file_db @dbname = 'test1',
@physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\test1_data.mdf'

The error is:
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'test1'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL\data\test1_Log.LDF' may be incorrect.

Thanks!

TT

 

[SQLBill]

You are probably going to have to restore from a backup.

-SQLBill

Posting advice: FAQ481-4875

 

[mrdenny]

Make sure that there is no file in that folder with the name test1_log.ldf. If there is, rename it. If there is not, you'll need to restore from backup.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com