I only see today's actions

gcleavesgcleaves Posts: 5
edited June 26, 2007 7:25AM in SQL Log Rescue
Hello,

I'm trying to see what was done to my DB about a week ago but Log Rescue only displays yesterday's actions. The .TRN file is over 6 GB in size and and .BAK is 9 GB. Why do I only see about 12 transactions from a 6 GB transaction backup?

Is there any way to see further back in time?
· Share on Twitter

Comments

  • Robert CRobert C Posts: 425 Silver 2
    Hi there,

    When you select the backup files, does the wizard show any warnings, such as "Selected backup file doesn't contain any information" or "Break in sequence"?

    Thanks,
    Rob
    Robert Chipperfield
    Red Gate
    · Share on Twitter
  • gcleavesgcleaves Posts: 5
    The first time I ran the project I did see something to the effect that the backup did not have a transaction log. But I tried again and did not get an error a second time. The project found the two backup files, .BAK and .TRN and selected them automatically.
    · Share on Twitter
  • Robert CRobert C Posts: 425 Silver 2
    I assume you're using the FULL recovery model, given you have a log backup, but could you check this?

    Normally you should be able to see all transactions available to you. I'm surprised this isn't the case if you aren't getting any warnings.
    Robert Chipperfield
    Red Gate
    · Share on Twitter
  • gcleavesgcleaves Posts: 5
    Yes, "Full".

    I created the backup this morning and then tried using Log Rescue. The transaction log is erased by SQL when a FULL backup is done, right? Maybe that is why I am only seeing the transactions since the last backup. But then I don't understand why .TRN file would be 6 GB in size.....
    · Share on Twitter
  • Robert CRobert C Posts: 425 Silver 2
    Just a thought: could it be you have a very long running transaction on your database? If so, two things could result:

    1) SQL Server can't truncate the transaction log prior to the start of that transaction, maybe explaining why it's so large;

    2) SQL Log Rescue won't show any uncommitted transactions

    The transaction log is certainly truncated on a log backup; I don't remember off the top of my head if this also applies to a full backup.
    Robert Chipperfield
    Red Gate
    · Share on Twitter
  • gcleavesgcleaves Posts: 5
    Robert wrote:
    Just a thought: could it be you have a very long running transaction on your database? If so, two things could result:

    1) SQL Server can't truncate the transaction log prior to the start of that transaction, maybe explaining why it's so large;

    2) SQL Log Rescue won't show any uncommitted transactions

    The transaction log is certainly truncated on a log backup; I don't remember off the top of my head if this also applies to a full backup.
    Indeed I am searching for a very long INSERT that caused a large table to quadruplicate. But it was committed.

    Oh well....
    · Share on Twitter
  • Robert CRobert C Posts: 425 Silver 2
    One thing that may work is to manually specify older backups and transaction log backups before today's full backup - it may be that SLR can then use the transactions found in those as well.

    I'm not promising (it's been a while since I worked on Log Rescue), but it might do the trick.
    Robert Chipperfield
    Red Gate
    · Share on Twitter
  • Robert CRobert C Posts: 425 Silver 2
    Update to my previous post: if you're in full recovery mode, full backups don't clear out the transaction log, only log backups do.

    This is quite an interesting article on it:

    http://www.simple-talk.com/sql/backup-and-recovery/pop-rivett-and-the-expanding-log-/
    Robert Chipperfield
    Red Gate
    · Share on Twitter
Sign In or Register to comment.