I only see today's actions

Undo for SQL Server.

Moderator: eddie davis

I only see today's actions

Postby gcleaves » Tue Jun 26, 2007 9:44 am

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?
gcleaves
 
Posts: 4
Joined: Tue Jun 26, 2007 9:40 am

Postby Robert » Tue Jun 26, 2007 10:15 am

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
Robert
 
Posts: 410
Joined: Mon Oct 30, 2006 11:15 am
Location: Cambridge, UK

Postby gcleaves » Tue Jun 26, 2007 10:21 am

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.
gcleaves
 
Posts: 4
Joined: Tue Jun 26, 2007 9:40 am

Postby Robert » Tue Jun 26, 2007 10:25 am

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
Robert
 
Posts: 410
Joined: Mon Oct 30, 2006 11:15 am
Location: Cambridge, UK

Postby gcleaves » Tue Jun 26, 2007 10:28 am

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.....
gcleaves
 
Posts: 4
Joined: Tue Jun 26, 2007 9:40 am

Postby Robert » Tue Jun 26, 2007 10:32 am

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
Robert
 
Posts: 410
Joined: Mon Oct 30, 2006 11:15 am
Location: Cambridge, UK

Re:

Postby gcleaves » Tue Jun 26, 2007 10:37 am

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....
gcleaves
 
Posts: 4
Joined: Tue Jun 26, 2007 9:40 am

Postby Robert » Tue Jun 26, 2007 10:39 am

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
Robert
 
Posts: 410
Joined: Mon Oct 30, 2006 11:15 am
Location: Cambridge, UK

Postby Robert » Tue Jun 26, 2007 12:25 pm

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
Robert
 
Posts: 410
Joined: Mon Oct 30, 2006 11:15 am
Location: Cambridge, UK


Return to SQL Log Rescue

Who is online

Users browsing this forum: No registered users and 0 guests