July 17, 2012 at 11:58 pm
Before taking a transaction log backup , i want to ensure whether any transaction is there which is not back up in last tlog backup .
so if there is any pending transactions which are not backup , then only i want to perform next TLOG backup , so how to find it whether there are any transaction which are not part of last tlog backup ? any T-sql , query statement for that ?
July 18, 2012 at 12:05 am
TLOG backup does not contain entries which are already backedup.
July 18, 2012 at 1:05 am
Why?
That complicates the log backup process, it complicates restores as you won't know what files to expect.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 18, 2012 at 1:06 am
Suresh B. (7/18/2012)
TLOG backup does not contain entries which are already backedup.
Not entirely true.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 18, 2012 at 1:21 am
In SQL server 2012 , there is an option to backup a log before any restore. I want to do this action provided there is an tail end of log backup where there are some transactions exist which are not still backed up , so i want to find count of this transaction and if such transactions are there , then only do a backup log else skip it.
July 18, 2012 at 1:25 am
Safer just to always take it.
There will always be something in the log, whether it's just the record of the previous log backup, a checkpoint or a user transaction. Figuring out what is in the log and whether it's important is not going to be easy.
If the data in that database is critically important, just take the tail-log by default.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 18, 2012 at 2:38 am
GilaMonster (7/18/2012)
Safer just to always take it.There will always be something in the log, whether it's just the record of the previous log backup, a checkpoint or a user transaction. Figuring out what is in the log and whether it's important is not going to be easy.
If the data in that database is critically important, just take the tail-log by default.
Yes , I do agree but my restore time will increase if every restore is prepend with the log backup.
so in my restore for SQL Server 2012 , if i can add a integelliance that if tail log is present then
do a log a backup first and then restore then that will be great
July 18, 2012 at 2:50 am
Log backups or restores take seconds unless there's GBs of data in the log. You're not likely to save significant amounts of time, and checking the log to see if there is something to be backed up will probably take as long as the log backup in the case of a near-empty log.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 18, 2012 at 3:25 am
And you're only ever taking the tail log backup in an emergency recovery situation anyway. Just take the backup and then use it as part of the recovery (if needed). If you're restoring to any other point in time, you don't need the tail log backup.
But, all this assumes there are already regular log backups in place. Is that the case here?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply