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
imSQrLy
imSQrLy
SSC-Enthusiastic
SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)

Group: General Forum Members
Points: 191 Visits: 473
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!"
Michael Oberhardt
Michael Oberhardt
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 93
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).
wolfen
wolfen
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 229
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.
Knut Boehnert
Knut Boehnert
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1098 Visits: 380
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.
eric.fung
eric.fung
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 141
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 ?
Knut Boehnert
Knut Boehnert
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1098 Visits: 380
I think James hasn't told us everything yet. BigGrin
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.
wolfen
wolfen
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 229
Knut Boehnert (7/14/2008)
I think James hasn't told us everything yet. BigGrin
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.
humbleDBA
humbleDBA
Old Hand
Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)

Group: General Forum Members
Points: 356 Visits: 1508
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.
Michael Oberhardt
Michael Oberhardt
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 93
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.
SuperDBA-207096
SuperDBA-207096
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1929 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....
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