Post reply

Transaction Log recovery

  • September 28, 2004 at 4:21 am

    #108725

    Hi,

    This is a question on the transaction log functionality - a real world scenario. I have quite few committed transactions that are yet to be flushed onto the disk. The transactions are written onto the transaction log and before the checkpoint occurs, the disk holding the transaction logs crashes. The backups of the DB helped regain all the data. However, how do I recover the committed transactions that has not yet been written onto my DB?

  • September 28, 2004 at 5:11 am

    #524600

    You should take the backup of the transaction log file soon after the crash and restore after you restored the data backups.

    --Jeswanth

    --------------------------------

  • September 28, 2004 at 3:04 pm

    #524707

    ...in addition to what Jeswanth suggested, also consider using the NO TRUNCATE option when you backup the transaction log.  Read up on it in Books Online.

  • September 28, 2004 at 11:34 pm

    #524738

    Thanks for the response. The issue is that the disk that holds the transaction log had crashed and the transaction log was no more available. In a case like this, the users have committed their transactions, the checkpoint has not happened and the transaction log is no more available. My concern is for these committed transactions.

    PS:The scenario happened while testing on a test server and hence no major lose. But we fear this scenario on the production server.

  • September 29, 2004 at 2:12 am

    #524742

    Is the transaction log backed up to a different physical disk or even better to an independent storage area (NAS, tape for instance)? If this is so once you have the database back online perform a transaction log backup then perform a recovery to a point in time before the crash.

    I always set backups to network drives for this specific reason as my tape backups occur only once a day so hardware failures can potentially mean losing an entire days worth of data, unacceptable in most cases.

     

    Cheers

    Chris

  • September 29, 2004 at 2:37 am

    #524749

    I agree with you. I'm sorry for not being very clear. The transaction log backup is set on a network drive. The backup is sceduled to happen every 1 hour.

    But the crash happened before the backup took place. So now, the committed transactions that were not flushed are not available on the backup either. So is there anyway I can avoid such situation.

    My colleague here who works on Oracle said they have the concept of multiplexing which saves them from such disaster.

     

Viewing 6 posts - 1 through 5 (of 5 total)

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