Home / server / Questions / 145276
In Process
user44127
Asked: 2010-05-26 14:19:38 +0800 CST

SQL Server transaction log backups,

  • 772

I have a question regarding the transaction log backups in sql server 2008. I am currently taking full backups once a week (Sunday) and transaction log backups daily. I put full backup in folder1 on Sunday and then on Monday I also put the 1st transaction log backup in the same folder. On tuesday, before I take the 2nd transaction log backup I move the first transaction log backup from folder1 an put it into folder2 and then I take the 2nd transaction log backup and put it in the folder1. Same thing on Wed, Thurs and so on. Basicaly in folder1 I always have the latest full backup and the latest transaction log backup while the other transaction log backups are in folder2. My questions is, when sql server is about to take, lets say 4th (Thursday) transaction log backup, does it look for the previous transac log backups (1st, 2nd, and 3rd) so that this new backup will only include the transactions from the last backup or it has some other way of knowing whether there are other transac log backups. Basically, I am asking this because all my transaction log backups seem to be about the same size and I thought that their size will depend on the amount of transactions since the last transaction log backup.

Example: If you have a, lets say, full backup and then you take a transac log backup and this transac log backup is lets say 200 MB and now you immediatelly take another transac log backup, this last transac log backup should be considerably smaller than the first one because no or almost no transaction occured between these two backups, right? At least, that's what I've been assuming. What happens in my case is that this second backup is pretty much the same size as the first one and I am wondering if the reason for that is because I moved the first transac log backup to a different folder so now sql server thinks that all I have is just a full backup and it then gets all the transactions that happened since the full backup and puts it in the 2nd transac log backup.

Can anyone please explain if my assumptions are right? Thanks...

backup logging sql

5 Answers

  1. Chris and joeqwerty, Thank you guys for your answers. I understand the difference between differential and log backup but I guess I didn't express myself clearly. I always thought that the log backups contain all the transactions since the last log backup (or full backup in case of the first log backup) and you just confirmed that so I guess I was right there. What threw me off is that I took a log backup this morning and then I had to take another (unplanned) log backup this afternoon and these 2 turned to be about the same size even though there shouldn't have been too much activity on the db (though I might be wrong about this). So basically, I was afraid that by moving the old log backup files to a different folder I am 'forcing' the SQL Server to grab all the transactions since the full backup (and essentially behave like a differential backup because that's what the 1st log backup is anyway) for each new log backup. So, I guess as long as SQL server tracks this info internally and I can move the files around I'm good. Thanks again...

    • 3

  2. I think you're confusing transaction log backups and differentials.

    A SQL log backup is the changes since the last log backup. To do a restore you'd need the full backup plus the full chain of log backups otherwise you can't restore to a point in time. SQL tracks everything internally so it doesn't matter if you move files around. The key thing is needing the full backup plus the unbroken chain of ALL log backups.

    A diff backup on the other hand will include all changes since the last full backup irrespective of any other diff or log backups that you've made in the interim.

    If you're traffic is consistent then your log backups could work out at approximately the same size since they're averaging the same volume of transactions in each.

    It goes without saying... but I'll say it anyway... test your backups frequently by trying some test restores - it's the best way to find out if your strategy has any holes in it.

    • 2

  3. SQL server doesn't pay any attention to the backup files on the hard drive when it performs any later backups. It keeps track of all that stuff internally inside the database log file.

    Yes (generally speaking) the size of the transaction log backup will depend somewhat on how much activity took place since the last log backup, but there may be a minimum size.

    But weekly fulls and daily log backups is kind of an unusual plan. Much more common would be weekly fulls and daily differentials, plus hourly log backups if you truly need point-in-time recovery. +1 on spencer's link.

    • 2

  4. A transaction log backup only truncates the inactive portion of the log. The definition of 'inactive' is what a lot of people seem to have trouble with. The inactive point is the area of the transaction log behind the oldest of: the most recent checkpoint or the start of the oldest non-committed transaction. For most databases the most recent checkpoint defines the inactive portion of the log.

    So, when does the DB checkpoint? Only when it has to. That is, when you shut it down, the log becomes too full, or the active portion of the log gets large enough that the DB thinks it would take longer than recoveryinterval to play it forward in case of a failure. The idea behind the infrequent checkpoints is that the DB knows that writing to disk is expensive, so it tries to keep as much in memory as it can.

    So, in your case, when you do two tlog backups back to back without either a natural checkpoint occurring or you forcing a checkpoint (with the checkpoint command) you're backing up the same chunk of transaction log twice because it's still considered active.

    • 2

  5. (This question is probably more suitable to serverfault.com rather than stackoverflow.)

    A backup of the transaction log ONLY backs up transactions NOT ALREADY backed up by a previous transaction log backup.

    That is, for a recovery, you will need the full backup AND each and every transaction log backup since the start of the full backup.

    HTH

    I think you may have transaction log backups confused with differential backups.

    http://technet.microsoft.com/en-us/library/aa337534%28SQL.100%29.aspx

    • 1