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 Sunday, July 13, 2008 7:17 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 9:25 AM
Points: 133, Visits: 462
Comments posted to this topic are about the item A Story of the Deleted Transaction Log

Jimmy

"I'm still learning the things i thought i knew!"
Post #533132
Posted Sunday, July 13, 2008 11:36 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 14, 2014 8:34 PM
Points: 19, Visits: 91
I've had a lot of experience with corrupt transaction logs. We have a client where they put out machines with faulty raid controllers. If the machine lost power, HDD corruption ensued.

Fortunately it was with SQL 2000, where you can actually rebuild a transactionlog (an undocumented stored proc).

Eg, for a corrupt MSDB transaction log, put the DB in emergency mode and:

DBCC rebuild_log('msdb','D:\MSSQL\Data\msdblog.ldf')

This even works with replicated DBs (without dropping replication).
Post #533258
Posted Monday, July 14, 2008 12:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 3:16 AM
Points: 2, Visits: 227
I am just wondering how the transaction log has been deleted during SQL server being running. The SQL server locks the data and log files and they cannot be moved or deleted.
Post #533281
Posted Monday, July 14, 2008 1:04 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, April 11, 2014 2:46 AM
Points: 832, Visits: 327
wolfen (7/14/2008)
I am just wondering how the transaction log has been deleted during SQL server being running. The SQL server locks the data and log files and they cannot be moved or deleted.

Quite easy. Detach the database - this commits all outstanding transactions in the transaction log and then takes the database offline. It is not visible in your database engine afterwards.

The way James described this incident it was very likely the saving grace of the technician.

Until the day I developed a script to run my backup and truncate the backup log I ran into this issue myself. Normal way was to detach the database, delete the log file and attach the database again. SQL Server didn't find the log transaction file so it recreated it empty.
Post #533292
Posted Monday, July 14, 2008 1:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 3:39 AM
Points: 16, Visits: 99
I have not come accross these sort of excited recovery scenario, and learn a lot from the experience shared from the article and responses.

In the past, I also come across the DB log growing too large. I found that the fastest way to shrink the DB log is like that :
- Stop the DB
- Detach the DB with sp_detach_db
- Delete / Rename the LDF file
- Attach the DB with sp_attach_single_file_db with the MDF file left.

Can sp_attach_single_file_db to be used in the mentioned scenario ?



Post #533294
Posted Monday, July 14, 2008 1:10 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, April 11, 2014 2:46 AM
Points: 832, Visits: 327
I think James hasn't told us everything yet. :D
What I find curious is that the technician was able to detach the database (otherwise he wouldn't have been able to delete the transaction log) but didn't know about the attach routine.
Post #533295
Posted Monday, July 14, 2008 1:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 3:16 AM
Points: 2, Visits: 227
Knut Boehnert (7/14/2008)
I think James hasn't told us everything yet. :D
What I find curious is that the technician was able to detach the database (otherwise he wouldn't have been able to delete the transaction log) but didn't know about the attach routine.


That's what I meant in my previous post.
Post #533303
Posted Monday, July 14, 2008 2:15 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 9:38 AM
Points: 200, Visits: 1,277
I would urge anyone who encounters a similar issue to read Paul Randal's blog (http://sqlskills.com/blogs/paul/) as the likelyhood is that the database will be in an inconsistent state. We had similar issues recently, on a system we didn't manage for the customer but were happy to try and help out with. Paul's blog was our first port-of-call, just to refresh ourselves on the steps we should take. Unfortunately, the database was not recoverable without data loss and unknown inconsistency, which wasn't acceptable to the customer, but not so important to call in data recovery experts. We had explained the issue of likely data inconsistency to the customer and they were able to act accordingly. BTW James, not saying that you were wrong here as you probably had no choice, but if the consistency of the database is critical because, say, its a banking system, then using data recovery experts may be the only way left to you in trying to get the deleted file back, in an effort to prevent unknown data inconsistency.

The DBCC CHECKDB ...REPAIR_ALLOW_DATA_LOSS... is the absolute last thing you should do (http://www.sqlskills.com/blogs/paul/2007/09/15/CorruptionLastResortsThatPeopleTryFirst.aspx), when all-esle has failed, and you should do your best to understand the consequences of this action on the database.

Post #533315
Posted Monday, July 14, 2008 2:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 14, 2014 8:34 PM
Points: 19, Visits: 91
I've had a few issues with rogue transaction logs as well (on MSDE at any rate). Even on autoshrink I've had them grow to over 10GB, and didn't even notice it.

They are pretty easy to deal with though - truncate them (WITH TRUNCATE ONLY) then shrink.

A lot of installations we have scheduled maintenance jobs that do this, and defrag indexes periodically.
Post #533317
Posted Monday, July 14, 2008 4:15 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 02, 2013 12:15 PM
Points: 1,443, Visits: 711
Wow! That sure was an interesting problem.... Glad it worked ok, I'd probably be up nights though worrying over that DB and if anything was lost....
Post #533374
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse