Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

A Story of the Deleted Transaction Log Expand / Collapse
Author
Message
Posted Monday, July 14, 2008 5:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 28, 2008 9:00 AM
Points: 4, 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...
Post #533397
Posted Monday, July 14, 2008 6:38 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 8:17 AM
Points: 297, Visits: 335
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
Post #533444
Posted Monday, July 14, 2008 6:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 3, 2014 10:19 AM
Points: 1, 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
Post #533446
Posted Monday, July 14, 2008 6:51 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, December 7, 2010 8:19 AM
Points: 327, Visits: 123
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.
Post #533458
Posted Monday, July 14, 2008 7:06 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 20, 2014 6:20 AM
Points: 215, Visits: 205
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.
Post #533463
Posted Monday, July 14, 2008 7:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 28, 2009 7:35 AM
Points: 6, Visits: 13
oberhardt is correct.

A maintenance plan should be set up to truncate, shrink, and backup the transaction log -- nightly, if necessary.
Post #533482
Posted Monday, July 14, 2008 8:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 1:10 PM
Points: 104, Visits: 130
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.

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. :D
Post #533537
Posted Monday, July 14, 2008 9:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 29, 2014 7:33 AM
Points: 3, Visits: 68
I believe that we can detach the database and attach the database back without a ldf file. Sql Service will create a log file.


Post #533619
Posted Monday, July 14, 2008 9:30 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 2, 2013 12:15 PM
Points: 1,443, 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..
Post #533650
Posted Monday, July 14, 2008 9:35 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, July 15, 2008 5:20 AM
Points: 91, 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



Post #533657
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse