September 2, 2010 at 6:02 am
I faced an interesting question in an interview the other day - the interviewer asked "We take transaction log backups we don't really have enough space to store them, so we often look at the backups and see if anything has really changed from the last tran log backup. If you were assigned this task, how would you find if anything has really changed in the database since the last transactional log backup?"
My understanding is that deleting a transaction log backup breaks the LSN chain and this question confused me. Anyone here knows the answer? Any pointers/links are appreciated.
September 2, 2010 at 6:29 am
Transaction log backups are incremental. Lets say you take a full backup at 8:00AM, and then transaction logs at 9:00AM, 9:30AM, and 10:00AM. At 10:25AM your sql server catches on fire, and now you need to restore to the database to the most recent time. If you deleted the 9:30AM transaction log backup, guess what, you can ONLY get the database back to whatever state it was in at 9:00AM even though you have the 10:00AM backup.
thanks
September 2, 2010 at 6:34 am
That is what my understanding is, and thats why I was confused when the interviewer talked about deleting the transaction logs. What I would like to know is - Is there any way to find whether *anything* changed in the database since the last tran log backup - looking at your backups or LSN numbers.
September 2, 2010 at 6:42 am
Yogirk (9/2/2010)
I faced an interesting question in an interview the other day - the interviewer asked "We take transaction log backups we don't really have enough space to store them, so we often look at the backups and see if anything has really changed from the last tran log backup. If you were assigned this task, how would you find if anything has really changed in the database since the last transactional log backup?"My understanding is that deleting a transaction log backup breaks the LSN chain and this question confused me. Anyone here knows the answer? Any pointers/links are appreciated.
hopefully the interviewer was looking for you to reply 'I would not bother because it would break the log chain and affect recovery'.
May have been trying to catch you out. If not, move on to the next interview or expect to inherit problems.
---------------------------------------------------------------------
September 2, 2010 at 6:47 am
Either the interviewer was trying to trap you (possible) or you've just found the first place you can help that business. Yes, deleting logs because "not much, or nothing, has changed" completely breaks the chain and restoring that system to a point in time after the delete is no longer possible.
"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
September 3, 2010 at 5:31 am
Yogirk (9/2/2010)
What I would like to know is - Is there any way to find whether *anything* changed in the database since the last tran log backup - looking at your backups or LSN numbers.
Yes. It SHOULD be possible.
For example: msdb.dbo.backupset has columns called first_lsn, last_lsn, checkpoint_lsn, database_backup_lsn.
And using DBCC LOGINFO or function ::fn_dblog it is possible to get the current LSN.
If the current LSN is higher than the last backup LSN, take log backup.
Unfortunately DBCC LOGINFO and ::fn_dblog return LSN in different format (hex) than msdb.dbo.backupset (decimal).
I hope others will pitch in and offer a solution to the original question.
(Someone had asked similar question in SSC two years ago and it is not solved )
September 3, 2010 at 6:03 am
In any case, the question is entirely invalidated by the fact that they say they want to do this as they don't have space to store them. If absolutely nothing has changed since the last TLog backup, then the backup size will be minuscule...
It sounds like they just don't understand what they're talking about. You need to work out if this is a company you can thrive in or not - for me this would be the sort of company I'd do consultancy for to set them straight and automate/fix all these inherent problems but I'd steer clear for a permanent position.
Interviews are as much for you to work out whether they are suitable for you as it is the other way round.
September 3, 2010 at 6:23 am
Thanks HowardW
September 3, 2010 at 9:52 am
Ok, a bit of humble pie eating here.
this is the output from msdb..backupset. a full backup was taken followed by 3 tranlog backups. Nothing changed between the full backup and the first tranlog backup, and the first tranlog and the second log backup. SOMETHING changed between the second log backup and the third log backup.
type first_lsn last_lsn checkpoint_lsn database_backup_lsn
---- ----------------------------- ----------------------------- ------------------------------- --------------------------
D 21000000027000037 21000000028600001 21000000027000037 21000000024700037
L 21000000024500001 21000000029000001 21000000027000037 21000000027000037
L 21000000029000001 21000000029000001 21000000027000037 21000000027000037
L 21000000029000001 21000000029300001 21000000027000037 21000000027000037
Guess what, you can restore the full backup, restore the first log backup and go straight to the third log backup and restore that. This will be because the first and last lsns are in order with no gaps (unbroken chain).
so the answer to the question is check the first and last lsns in each log backup via msdb.backupset, if they don't change from the previous log backup you can delete the backup.
Mind you the backup would be tiny so space saved would be not enough to help you, and who in their right minds is going to delete backups?
issuing a checkpoint does not affect this.
---------------------------------------------------------------------
September 3, 2010 at 1:17 pm
george sibbald (9/3/2010)
Ok, a bit of humble pie eating here.this is the output from msdb..backupset. a full backup was taken followed by 3 tranlog backups. Nothing changed between the full backup and the first tranlog backup, and the first tranlog and the second log backup. SOMETHING changed between the second log backup and the third log backup.
type first_lsn last_lsn checkpoint_lsn database_backup_lsn
---- ----------------------------- ----------------------------- ------------------------------- --------------------------
D 21000000027000037 21000000028600001 21000000027000037 21000000024700037
L 21000000024500001 21000000029000001 21000000027000037 21000000027000037
L 21000000029000001 21000000029000001 21000000027000037 21000000027000037
L 21000000029000001 21000000029300001 21000000027000037 21000000027000037
Guess what, you can restore the full backup, restore the first log backup and go straight to the third log backup and restore that. This will be because the first and last lsns are in order with no gaps (unbroken chain).
so the answer to the question is check the first and last lsns in each log backup via msdb.backupset, if they don't change from the previous log backup you can delete the backup.
Mind you the backup would be tiny so space saved would be not enough to help you, and who in their right minds is going to delete backups?
issuing a checkpoint does not affect this.
Interesting... never seen a test like that before. Good to know, but, sort of pointless. So they can delete the log file that doesn't contain any information and reclaim it's non-existant wasted space... cool!
"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
September 3, 2010 at 1:18 pm
Oooh, rereading it that came across a little bad.
I meant pointless for the people trying to save space.
Not the test. The test was great!
"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
September 3, 2010 at 1:29 pm
I would also look at this way...
If reviewing the transaction log backups consistently show that a particular period time of day they don't backup anything, then its a waste of time not just in deleting them but having a backup during that period. They (or someone) should know the application enough to now when it is actively handling transactions, so instead of wasting time in the day checking those log backups why not just change the backup schedule to be more effective?
However, don't feel like you are alone in responding to that question. Reading it prior to going down the post I thought about it and had your same mindset on it. 😉
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 3, 2010 at 2:14 pm
I knew what you meant Grant.
Thanks for the feedback. 🙂
---------------------------------------------------------------------
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply