Transaction log backups

Question

Transaction log backups

Yogirk

SSC Veteran

Points: 272
More actions
September 2, 2010 at 6:02 am

#227329

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.

Viewing 13 posts - 1 through 12 (of 12 total)

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

SQL Stud SSC Eights! Points: 987 More actions · Answer 1

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

Yogirk SSC Veteran Points: 272 More actions · Answer 2

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.

george sibbald SSC Guru Points: 104210 More actions · Answer 3

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.

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

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 4

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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Suresh B. SSC-Insane Points: 22986 More actions · Answer 5

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 )

HowardW One Orange Chip Points: 29109 More actions · Answer 6

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.

Suresh B. SSC-Insane Points: 22986 More actions · Answer 7

Suresh B.

SSC-Insane

Points: 22986

September 3, 2010 at 6:23 am

#1216118

Thanks HowardW

george sibbald SSC Guru Points: 104210 More actions · Answer 8

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.

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

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 9

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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 10

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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Shawn Melton SSC-Insane Points: 24675 More actions · Answer 11

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

george sibbald SSC Guru Points: 104210 More actions · Answer 12

I knew what you meant Grant.

Thanks for the feedback. 🙂

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