A Story of the Deleted Transaction Log

  • imSQrLy

    Ten Centuries

    Points: 1335

    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

    SSC Eights!

    Points: 999

    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

    SSC Enthusiast

    Points: 122

    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

    SSCrazy

    Points: 2946

    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

    SSC Veteran

    Points: 239

    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

    SSCrazy

    Points: 2946

    I think James hasn't told us everything yet. 😀

    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

    SSC Enthusiast

    Points: 122

    Knut Boehnert (7/14/2008)


    I think James hasn't told us everything yet. 😀

    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

    Hall of Fame

    Points: 3440

    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

    SSC Eights!

    Points: 999

    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

    SSCrazy Eights

    Points: 8176

    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....

  • Sergio-729493

    SSC Journeyman

    Points: 90

    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

    SSCarpal Tunnel

    Points: 4582

    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

    Valued Member

    Points: 51

    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

    SSCrazy

    Points: 2760

    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

    SSC Veteran

    Points: 289

    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.

Viewing 15 posts - 1 through 15 (of 114 total)

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