SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A Story of the Deleted Transaction Log


A Story of the Deleted Transaction Log

Author
Message
Sergio-729493
Sergio-729493
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 15
Couldn't it be possible that the technician just stopped the SQL service, deleted the file (the biggest one) and then restarted the service ? (it's not the first time I've seen this). That would also make sense since no data seemed to be lost...
BobAtDBS
BobAtDBS
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1562 Visits: 372
Why wouldn't you simply detach the database, then attach it back with the single file option? Sorry, I'm in a hurry this morning, but I'm almost certain I've moved databases that way without a transaction log.


Student of SQL and Golf, Master of Neither
Brian Baumann
Brian Baumann
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 16
Awesome article. Fortunately enough, I have not encountered such an issue but good to know the steps needed.

Do these steps ring true for 2005 & 2000?

-B
Martin Vrieze
Martin Vrieze
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1310 Visits: 125
Very interesting.

As an experienced DBA...were you jotting notes along the way as you stepped (er, fumbled) your way through this fix?

How long did the entire scenario take to get the DB back up and running again?

What non-BOL resources did you use to help solve the issue?

I'm alway interested in these additional details because I often see impatience with staff waiting for a fix to complete...they wonder if it is working properly and want to cancel and try something else. Very often, this impatience leads to more/worse problems than the original problem itself.
Raymond Xu-564704
Raymond Xu-564704
SSC Veteran
SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)

Group: General Forum Members
Points: 255 Visits: 216
There are two ways to recover a single data file DB in SQL 2005.

1. Create DB for attach ...

CREATE DATABASE database_name
ON [ ,...n ]
FOR { ATTACH [ WITH ]
| ATTACH_REBUILD_LOG }

2. If the log was lost during SQL server is down, once you start the sql server, a new log file will be generated automaticly.
Bill Whitman
Bill Whitman
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 13
oberhardt is correct.

A maintenance plan should be set up to truncate, shrink, and backup the transaction log -- nightly, if necessary.
Lee Fisher
Lee Fisher
SSC Veteran
SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)

Group: General Forum Members
Points: 206 Visits: 160
I've had this happen before, when SQL was stopped to delete the log file you got as much of a commit as it possible, usually the log file has committed all transactions and if it hasn't there isn't much you can do. Ermm

The fastest way to bring the database back online in this situation is:
1. Stop SQL and rename the mdf file
2. Restart SQL and delete suspect database (possibly have to restart SQL)
3. Rename the mdf to it's original name
4. Use sp_attach_single_file_db to bring
5. Do a full backup.

The next step is to take a baseball bat to the fingers of the sysadmin who did this so they will remember to call the DBA next time. BigGrin
lehanhsieh
lehanhsieh
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 70
I believe that we can detach the database and attach the database back without a ldf file. Sql Service will create a log file.



SuperDBA-207096
SuperDBA-207096
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4359 Visits: 711
Bill Whitman (7/14/2008)
oberhardt is correct.

A maintenance plan should be set up to truncate, shrink, and backup the transaction log -- nightly, if necessary.


Probably don't want to shrink the log unless you absolutely have to..
BarryOC
BarryOC
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 17
Hello,
They should not have been able to delete the LDF unless they stopped the service. First step should be to find out what the Tech did. I regularly use the detach/attach scenario to kill a LDF and start it at ) but we are a special case. On regular production data it is iffy.
Best wishes,
Barry



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search