TL;DR
Unless you’re on SQL 2000, don’t worry about scheduling log backups during full backups
Log backups during full backups won’t truncate the transaction log
You want to keep taking log backups in case your full backup fails
The first time I ever set up backups
Was, unfortunately, using a maintenance plan. All of the databases were in simple recovery. It was largely used for staging and tenderizing data during ETL. No log backups need apply.
Fast forward a bit, and I’m setting up backups for a server where losing 30 minutes of data could set a project back several hours. We’re now hiring log backups.
So there I was, dutifully creating extravagant maintenance plans, pondering the miracle of the differential backup, and the grand eloquence of log backups. They were running every 10 minutes, those log backups.
Every 10 minutes.
Even during full backups.
I’m a developer and what is this?
- What is SQL going to do with those?
- Do I have to restore them?
- Should I pause log backups during full backups?
- Will this break something?
- How much Laphroaig do I have to drink to forget I thought about this?
This was confounding to me. So I did some digging. Back in the SQL 2000 days, this could have gotten weird. But I was, thankfully(?) on 2005. Heh. Yeah. 2005. I know.
After 2005, you can totally, absolutely, 100% take log backups during full backups. You can take them and nothing will break and you won’t have to restore them (unless your full backup fails).
Wanna see a demo?
Of course you wanna see a demo. This one is a little more complicated than usual. It will require several-SSMS-tab technology.
|
1 2 3 4 5 |
ALTER DATABASE [StackOverflow] SET RECOVERY FULL BACKUP DATABASE [StackOverflow] TO DISK = 'D:\Backup\Crap\StackOverflow\StackOverflow.bak' WITH CHECKSUM, COMPRESSION, STATS = 1 |
I usually keep my SO database in simple, because I do horrible things that I don’t want to fully log. Fun fact: if you switch from simple to full recovery model and don’t take a full backup, you’re basically still in simple recovery. You should think about that for a minute and then take steps to avoid getting fired. Backing up SO takes me about 3.5-4 minutes.
In another window, kick off some log backups 1 minute apart. Note that doing this won’t overwrite log backups, it will stack them all within a single file.
You can verify this behavior by running the RESTORE HEADERONLY command at the end of this block. If you want to restore a particular backup out of a file with multiple backups in it, you use the position column and specify it with FILE = [n], which you can read more about here.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
BACKUP LOG [StackOverflow] TO DISK = 'D:\Backup\Crap\StackOverflow\StackOverflow_log.trn' WITH COMPRESSION, CHECKSUM, INIT, FORMAT WAITFOR DELAY '00:01:00.000' BACKUP LOG [StackOverflow] TO DISK = 'D:\Backup\Crap\StackOverflow\StackOverflow_log.trn' WITH COMPRESSION, CHECKSUM WAITFOR DELAY '00:01:00.000' BACKUP LOG [StackOverflow] TO DISK = 'D:\Backup\Crap\StackOverflow\StackOverflow_log.trn' WITH COMPRESSION, CHECKSUM WAITFOR DELAY '00:01:00.000' BACKUP LOG [StackOverflow] TO DISK = 'D:\Backup\Crap\StackOverflow\StackOverflow_log.trn' WITH COMPRESSION, CHECKSUM WAITFOR DELAY '00:01:00.000' BACKUP LOG [StackOverflow] TO DISK = 'D:\Backup\Crap\StackOverflow\StackOverflow_log.trn' WITH COMPRESSION, CHECKSUM WAITFOR DELAY '00:01:00.000' BACKUP LOG [StackOverflow] TO DISK = 'D:\Backup\Crap\StackOverflow\StackOverflow_log.trn' WITH COMPRESSION, CHECKSUM RESTORE HEADERONLY FROM DISK = 'D:\Backup\Crap\StackOverflow\StackOverflow_log.trn' |
Just so you know I’m not pulling any shenanigans, let’s generate some log activity. This will dump 100 rows into a dummy table every 30 seconds. It is neither pretty nor elegant.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
USE [StackOverflow] IF OBJECT_ID('dbo.YouBigDummy') IS NOT NULL DROP TABLE [dbo].[YouBigDummy] CREATE TABLE [dbo].[YouBigDummy] ( ID INT IDENTITY(1,1), [Name] VARCHAR(50) DEFAULT 'You', CreateDate DATETIME DEFAULT GETUTCDATE() ) INSERT dbo.[YouBigDummy] ( [Name], [CreateDate] ) DEFAULT VALUES GO 100 WAITFOR DELAY '00:00:30.000' GO INSERT dbo.[YouBigDummy] ( [Name], [CreateDate] ) DEFAULT VALUES GO 100 WAITFOR DELAY '00:00:30.000' GO INSERT dbo.[YouBigDummy] ( [Name], [CreateDate] ) DEFAULT VALUES GO 100 WAITFOR DELAY '00:00:30.000' GO INSERT dbo.[YouBigDummy] ( [Name], [CreateDate] ) DEFAULT VALUES GO 100 WAITFOR DELAY '00:00:30.000' GO INSERT dbo.[YouBigDummy] ( [Name], [CreateDate] ) DEFAULT VALUES GO 100 WAITFOR DELAY '00:00:30.000' GO INSERT dbo.[YouBigDummy] ( [Name], [CreateDate] ) DEFAULT VALUES GO 100 WAITFOR DELAY '00:00:30.000' GO TRUNCATE TABLE [dbo].[YouBigDummy] |
When that’s all done, you can run something like this to see what happened. You’ll probably have to replace the date. I wrote this, like, two or three weeks ago by now.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT [b].[database_name] , [b].[backup_start_date] , [b].[backup_finish_date] , [b].[type] , [b].[first_lsn] , [b].[last_lsn] , [b].[checkpoint_lsn] , [b].[database_backup_lsn] FROM [msdb].[dbo].[backupset] AS [b] WHERE [b].[database_name] = 'StackOverflow' AND [b].[backup_start_date] >= '2015-12-02 17:30:00.000' ORDER BY [b].[backup_start_date]; |
When you take a full backup, the first thing it does it issue a checkpoint. That’s why the full and all subsequent log backups have the same checkpoint LSN. The first four log backups all have the same database backup LSN because they occurred during the full backup. That doesn’t change until the full is done.
For toots and snickers, I ran this all a second time, and cancelled the full backup halfway through. The full backup issued a new checkpoint, so the checkpoint LSN changes, but the database backup LSN never changes, because it got canceled. That means taking log backups during full backups is totally useful. If your full backup fails for whatever reason, these things keep the chain alive.
If the third time is to be a charm, and it is, the same thing occurs as the first run. New checkpoint LSN, and the database backup LSN runs through until the backup finishes. You can verify that by looking at the start and end times columns.
If you still don’t believe me
Just look at sys.databases while you’re running a full backup.
|
1 2 3 4 5 |
SELECT [name], [log_reuse_wait] , [log_reuse_wait_desc] FROM [sys].[databases] WHERE [name] = 'StackOverflow'; |
The result is acknowledged, though not documented, here. This does indeed mean that log truncation will not occur during a full backup even if you take log backups. It will happen when you take the first log backup after the full finishes. You may want to consider this when scheduling maintenance items that may hog up log space alongside full backups.
Recap
Backups are beautiful things. You should take full ones, and probably differential ones, and if you’re in full recovery model, definitely log ones. How often you take them is up to you and your boss. Or maybe their boss. But it’s definitely not up to you. Unless you’re your boss.
Log backups during full backups won’t hurt anything, and may end up helping things if your full backup fails, and you need to restore something.
Log backups during full backups will not truncate the log. That has to wait until the first log backup after the full finishes.
27 Comments. Leave new
Wow, I had to say “LSN Chains” to myself a few times to get that.
Do you guys have any snippets or tutorials on how to quickly identify backup sets? (F + D + L + L + L) and (F + L + L + L)
http://dba.stackexchange.com/questions/72854/restore-all-databases-script/72857#72857
My answer to this question should get you up and running. This script, along with all the BO collection and sp_whoisactive is one of the first things I deploy to new servers.
This is very interesting. It is one of those things you “just knew” but were afraid to ask the details on. Thanks!
Red Squares At Night? Now I can’t get that song by The Fixx out of my head.
Isn’t it also true that the log backups that happen during the full backup are useful if you need to restore to a point in time between the start and end time of that full backup?
Its really interesting and helpful…
Thanks!!!
This is very helpful to understanding why I have a huge log backup during a full backup that was taking up so much space! The solution was to schedule my log shipping to start after the full backup. For a long time I did not understand why there was a huge log backup after a full backup, but only to realize that it was during a full backup that was causing this.
THIS was one of the most helpful articles I ever read. Thanks.
Just catching up on this, great article thank you. I was actually looking for a solution to a related issue. When we add a new database (restore from a staging instance) to our live instance we have a job which sets it to Full recovery (in case the tech forgot that) and then a maintenance plan that runs a Full backup each night. We also have a maintenance plan that runs a transaction log backup every 30 minutes. How do I stop the latter failing whilst we are waiting for the first Full backup to complete? I’m thinking I maybe cannot achieve this with maintenance plans but need a bespoke agent job?
Paul – for questions about things we’re not blogging about, head on over to https://DBA.StackExchange.com.
Thanks, didn’t mean top thread-jack, see you in the other place…
“Log backups during full backups will not truncate the log. That has to wait until the first log backup after the full finishes.”
could you replace “full” with “differential” here? is the way backups function the same in regards to when new transaction logs start? i am having trouble understanding how a restore would work, because i keep asking “how do you get a clean cutoff point between the end of a differential backup and the start of the very next transaction log?”
eric — LSNs are a hell of a thing.
Think about a scenario with Log Shipping: If you take a differential back up on the primary and apply it to the secondary, which log file would you try to restore next?
if i just restored a diff, that means i have already restored a full. so i would restore the first log file that was backed up after the diff? i mean, i know that the backup software is doing this for me but i need to wrap my head around it so i understand. thanks for responding 🙂
Right.
Full, most recent Diff, any Log files after the Diff.
RedGate has a pretty decent PDF that explains the basics of how it all works.
“I’m always convinced that whoever came up with the term LSN Chains really liked 90’s Grunge.”
L-O-L. Thanks for the laugh and helping me feel less old!
> How much Laphroaig do I have to drink to forget I thought about this?
Most accurate statement ever.
[…] – transaction log file not shrinkingsource – transaction log backup during full […]
Hi Erik,
I have primary database already set with replication and we have already jobs for Full(weekly), Diff(night) & log(1 hrs) backup plans. Now I want to set up another copy of the same database as read only mode not for DR. May I go with log shipping by using same log backups without changing any previous setup.
Vinay- for questions, head to https://DBA.stackexchange.com.
Similar question: We have a very large database with a very large (over 1.5 TB) log. A transaction log dump was started, and while it is running, a full DB backup was run (successfully). The log dump is still running. If the log dump is killed and we truncate the log, at what point will transactions be lost? After the full dump started?
Susan – for questions, head to https://DBA.stackexchange.com.
[…] View Image More Like This […]
Weirdly, I’m finding that a COPY_ONLY backup is blocking a non-COPY_ONLY log backup from finishing, which doesn’t make sense to me
Do you know why transaction log cannot be truncated while full backup is running? I mean if we would run transaction log backup while full is running. I understand that some transactions will be backed up with full, to ensure it’s conssitency and full backup needs them, but why e.g. inactive VLFs are not truncated which are alredy backed up by full backup? Is it just lazy engineers do not want to implement this or there are some technical reasons why it would not be possible? Also one more option to be able to truncate log dring full backup would porbably be to do it as in oracle – make full backup without transaction log (which would leave full backup inconsistent without later log backup), this would allow to backup and truncate log simultaneously while running full, as it does not need logs.
If you want to know why Microsoft hasn’t built something, you’ll need to contact Microsoft, not a guy with a blog. 😉