A Story of the Deleted Transaction Log

Question

A Story of the Deleted Transaction Log

Viewing 15 posts - 31 through 45 (of 113 total)

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

imSQrLy Ten Centuries Points: 1335 More actions · Answer 1

I will try this. Did not know about it. Probably would have saved some time and wouldn't have to have used the data loss option.

Jimmy

"I'm still learning the things i thought i knew!"

imSQrLy Ten Centuries Points: 1335 More actions · Answer 2

I am not sure why number 2 did not happen on its own. I was not rebuilding the log on its own.

Jimmy

"I'm still learning the things i thought i knew!"

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 3

Awesome article and an interesting escapade. Of course, it would have been much nicer if the technician had put it in a simple recovery model and then shrank the log file, but that would be too much to ask.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

Charles Kincaid SSChampion Points: 13593 More actions · Answer 4

Great atricle Jimmy.

I had this sort of thing happen on 2000. I posted something on this about it being detached and the ldf lost. I did the single file restore trick and then moved the files to the right places.

In 2005 there is procedure about stopping the service, deleting the ldf and then restarting the service. Scares the hell out of me and I raised a ruckus when it was suggested that we put it into our corporate knowledge base.

ATBCharles Kincaid

Greg-616990 SSC Enthusiast Points: 175 More actions · Answer 5

Frankie you are not right.

In most cases SQL Server will recreate the log file when you attach database without it, however sometimes it doesn't work.

I had an issue in the past when database was detached and log file deleted. SQL server didn't attach database at all.

Eventually I did exactly the same as was written in this article.

jaideep SSC Journeyman Points: 78 More actions · Answer 6

James,

Why couldn't we use the sp_attach_single_file_db or create database for attach command if we have the mdf file with no ldf file?

Won't this process be more safe than the process you listed in your post since this process would not involve any data loss?

Please correct me if I am wrong?

Regards

Jaideep

Michael Oberhardt Ten Centuries Points: 1109 More actions · Answer 7

jaideep (7/14/2008)
James,
Why couldn't we use the sp_attach_single_file_db or create database for attach command if we have the mdf file with no ldf file?
Won't this process be more safe than the process you listed in your post since this process would not involve any data loss?
Please correct me if I am wrong?
Regards
Jaideep

I've honestly never had any luck attaching a normal two file DB as a single file after the corruption of a transaction log. I always get errors (I can't recall exactly what, like it is saying the DB is a two part DB perhaps).

However, due to those faulty raid controllers at one of our clients, I've had a lot of experience recovering replicated DBs and system DBs with out even stopping the SQL service, dropping replication, or detaching the DB (over 20 in the last year) so it only takes me about 15-30 minutes now. I've even had corrupt MSDB DBs that I've had to deal with (it stops replication as the job lives in there), where I've recovered the DB while the site remained operational, and replication restarted once I recovered the MSDB.

The basic steps (with everything running, and a DB marked as suspect due to a transaction log):

- Put the DB in emergency mode

- Recreate the transactionlog using the undocumented system stored proc

- Put it in single user mode and run DBCC checks and fixes if required

- Put it back in multi user mode

.

Frankie-464050 Ten Centuries Points: 1010 More actions · Answer 8

Something that I don't understand.

In a production environment the failed database cannot be renamed to something else (too many connection & application stuff would be dependant on the database name.

So in the article he creates a new database - but you cannot create one using the name of the old failed database SQL will not let you.

How does one get around that little detail in a production world and bring a system back up using the articles steps.

According to the article he deletes the production mdf file.

Did some testing and after creating a new database;

renamed the new LDF to the failed DB name LDF,

deleted the newly created mdf,

renamed the failed mdf back to original name and proceeded as in article. DB came back up OK.

Greg-616990 SSC Enthusiast Points: 175 More actions · Answer 9

Frankie (7/14/2008)
Something that I don't understand.
In a production environment the failed database cannot be renamed to something else (too many connection & application stuff would be dependant on the database name.
So in the article he creates a new database - but you cannot create one using the name of the old failed database SQL will not let you.
How does one get around that little detail in a production world and bring a system back up using the articles steps.
?

It is possible. Simply when he received a call from one of his technicians database was already offline. So he could easly rename the mdf file. When you datach database SQL doesn't know anything about it, therfore you can create a new database with the same name.

Then when you can detach it, rename / replace mdf file and atach it back. SQL will not know about the change you did.

garyjill Valued Member Points: 57 More actions · Answer 10

Has anyone ever tried to use any "Undelete" tools to recover the deleted file? I haven't used anything on that large of file, but I've used them to recover other types of files. I once recovered a gal's full year's worth of homework she was storing on a flash drive after it got formatted.

Thanks,

Gary

Tariq Ahmed Grasshopper Points: 18 More actions · Answer 11

Hi James, great article. What company do you work for? We're in need of an external company to provide DBA support.

Thx.

sudha-426098 Valued Member Points: 60 More actions · Answer 12

Hello

I had a similar experience. Transaction log had grown too big. I was unable to backup database. But transaction log backup was taken. Then I was not able to take database offline and detach. But in the process the database was not visiblt in the Management studio. So I tried to delete log file. But it was in use and could not be done. So I just stopped the Sql server service and deleted ldf file. After this I was unable to attach mdf file. All the methods I tried failed. Fortunately I had the 2 days old complete database backup. This was only a temporary relief. 2 days of data had to be recovered. Finally the following url helped in recovering the database completely.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1045929&SiteID=1

We got the database back in read-only , emergency mode. Transferred the entire data to another database. All these activities were carried out in another system altogether.

sam_mastermind SSCommitted Points: 1975 More actions · Answer 13

can u really make a emergency mode database to single user mode in sql server 2000, i have tried it but it failed. Can u help in this regard.............

imSQrLy Ten Centuries Points: 1335 More actions · Answer 14

In SQL 2000 you can still set a db to emergency. You must use the following script to do so.

EXEC sp_configure 'allow updates', 1

RECONFIGURE WITH OVERRIDE

GO

update sysdatabases set status = 32768 where name = 'dbname'

Jimmy

"I'm still learning the things i thought i knew!"

shyam.babu SSC Rookie Points: 47 More actions · Answer 15

shyam.babu

SSC Rookie

Points: 47

August 7, 2009 at 1:44 am

#1035814

Kill all leave alone the one who is the one