September 14, 2016 at 1:44 am
Hi,
I've been doing a lot of reading about backups and transactions logs recently, and I have a couple of very simple questions that I'm hoping someone can help me definitively answer.
Here's how I understand the process to be:
If I have a database that's uses full recovery and that hasn't had a full backup yet, then taking a full backup will start the log chain. After that point, I can take differentials (or continue to take full backups) and I still have a valid log chain. Does that all seem correct?
If so, where does that leave transaction logs? If they don't get truncated until they're backed up, then it's possible the transaction log could exceed the database size itself, right?
So, while I might have a valid log chain that includes a full backup that's 10GB and a differential that's 1GB, the transaction log (backup) could theoretically be 20GB, right?
At this point, the log chain would be good and the transaction log might have been truncated, but it's still going to be 20GB. Does this mean that DBCC SHRINKFILE (which I'm reading over and over is not a good idea for ongoing maintenance) is the right thing to do in this type of scenario?
Also, is my understanding correct or am I missing something (or maybe a few things....?).
Thanks so much,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
September 14, 2016 at 2:17 am
Mike
Yes, your understanding is correct. That's (one of the reasons) why it's important to make regular transaction log backups. DBCC SHRINKFILE is indeed not recommended for regular use, but in the case where log backups haven't been set up correctly and the log file has grown larger than it would otherwise have needed to be, shrinking the file as a one-off is OK.
John
September 14, 2016 at 2:38 am
John,
Thanks for the response. Two follow-up questions:
1) Let's say the steps were, at a high level:
a) Take full backup
b) Take differential backup
c) Take log backup (huge, needs to be shrunk)
d) shrink log file
e) Take full backup
f) Take differential backup
g) Take log backup (good this time, small)
After step e) or even step g) after the log file has been shrunk and full, differential, and log backups have been taken again, is that initial huge log backup still necessary to keep around and take up disk space?
2) I've read that sometimes when DBAs run DBCC SHRINKFILE that the log file itself doesn't actually reduce in size and therefore isn't reflected when running exec sp_helpfile;
Have you ever experienced or heard of this, and do you know what the trigger is for it to reflect the new size?
Again, thank you.
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
September 14, 2016 at 2:51 am
Mike
The log backup will only be huge if you haven't backed it up for a long time (or, more accurately, if there has been a lot of changes in the database since the last log backup). Speak to the stakeholders of the database and find out what's the most data they can afford to lose (in terms of minutes) in the event of disaster and then schedule your log backups to run with (at least) that frequency.
Sometimes you can back up the log file and free space won't be released. In such cases, you can get a clue about what's stopping it by running this:SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = 'MyDatabase'
I recommend you read through Gail Shaw's articles on managing transaction logs. You should be able to find them with a simple search.
John
September 14, 2016 at 3:21 am
Thanks, John.
I actually just read Gail Shaw's article yesterday, and I know that I'll need to talk to business users to determine RPO. However, my question is whether the incredibly large log backup from step c) is needed. In other words, from a technical perspective, would it be breaking the log chain to not have that file? Wouldn't step e) forward account for anything that would have been in the huge log backup?
Also, I'm not trying to suggest doing this on an ongoing basis--I'm just trying to see if there would actually be any data loss by removing this bloated log, once correct backup procedures are in place.
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
September 14, 2016 at 3:24 am
Mike Scalise (9/14/2016)
In other words, from a technical perspective, would it be breaking the log chain to not have that file? Wouldn't step e) forward account for anything that would have been in the huge log backup?
If you're absolutely confident that the full backup (e) is good (restorable, undamaged) and that you will never have to restore to before (e), then you can delete it.
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
September 14, 2016 at 3:33 am
Gail,
Thank you! I just wanted to make sure I understood the implications. I'll of course make sure I have good backups (and practice restoring them multiple times) before considering deleting the large transaction log backup.
John,
That query was super helpful. It showed me exactly what it was waiting on.
Thank you both!
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
September 29, 2016 at 2:27 pm
John Mitchell-245523 (9/14/2016)
MikeThe log backup will only be huge if you haven't backed it up for a long time (or, more accurately, if there has been a lot of changes in the database since the last log backup). Speak to the stakeholders of the database and find out what's the most data they can afford to lose (in terms of minutes) in the event of disaster and then schedule your log backups to run with (at least) that frequency.
Sometimes you can back up the log file and free space won't be released. In such cases, you can get a clue about what's stopping it by running this:
SELECT log_reuse_wait_descFROM sys.databases
WHERE name = 'MyDatabase'
I recommend you read through Gail Shaw's articles on managing transaction logs. You should be able to find them with a simple search.
John
In a development database I'm working with, I initially sized the transaction log at 500MB and, because I wasn't performing log backups, it has grown to 647MB. I ran a log backup, which was 647MB in size and then:
DBCC SHRINKFILE (log_name, 500);
GO
Then, I ran the query John shared:
SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = 'MyDatabase'
Beforehand, it returned "LOG BACKUP" It now returns "NOTHING" which I expected and which I was happy about because I thought for sure when I queried sys.database_files, it would show that it was back down to 500MB. It's not--it's still at 647MB. Also, I checked at the file system level and it's 647MB there too.
Again, I'm sure there's a good reason. Can anyone shed some light?
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
September 29, 2016 at 4:26 pm
Mike Scalise (9/29/2016)
John Mitchell-245523 (9/14/2016)
MikeThe log backup will only be huge if you haven't backed it up for a long time (or, more accurately, if there has been a lot of changes in the database since the last log backup). Speak to the stakeholders of the database and find out what's the most data they can afford to lose (in terms of minutes) in the event of disaster and then schedule your log backups to run with (at least) that frequency.
Sometimes you can back up the log file and free space won't be released. In such cases, you can get a clue about what's stopping it by running this:
SELECT log_reuse_wait_descFROM sys.databases
WHERE name = 'MyDatabase'
I recommend you read through Gail Shaw's articles on managing transaction logs. You should be able to find them with a simple search.
John
In a development database I'm working with, I initially sized the transaction log at 500MB and, because I wasn't performing log backups, it has grown to 647MB. I ran a log backup, which was 647MB in size and then:
DBCC SHRINKFILE (log_name, 500);
GO
Then, I ran the query John shared:
SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = 'MyDatabase'
Beforehand, it returned "LOG BACKUP" It now returns "NOTHING" which I expected and which I was happy about because I thought for sure when I queried sys.database_files, it would show that it was back down to 500MB. It's not--it's still at 647MB. Also, I checked at the file system level and it's 647MB there too.
Again, I'm sure there's a good reason. Can anyone shed some light?
Thanks,
Mike
Your log file may not have shrunk as the currently active virtual log files may have been at the back of the logfile. You can run DBCC loginfo to see the number and status of the VLFs.
September 29, 2016 at 5:40 pm
Thanks, Lynn.
I ran DBCC LOGINFO and noticed that I have one status of "2" on my last FseqNo, so if the VLF can't be overwritten, it looks like I can't easily shrink the log. However, I did some research and it seems like many of the responses indicate that I need to continue to do transaction log backups until that "2" becomes a "0" ? I've already done several and it's not changing. Any thoughts on how I can handle this without changing the recovery mode of the database itself?
Thanks for showing me why it's happening.
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
September 29, 2016 at 5:41 pm
Also, I don't have any open transactions...
Mike Scalise, PMP
https://www.michaelscalise.com
September 30, 2016 at 1:54 am
Log backups *and* additional write activity on the DB such that the active VLF wraps around. You can't move VLFs, and you can't ever make the one that's active and in-use inactive (it's where the log records are being written to)
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
September 30, 2016 at 3:31 am
GilaMonster (9/30/2016)
Log backups *and* additional write activity on the DB such that the active VLF wraps around. You can't move VLFs, and you can't ever make the one that's active and in-use inactive (it's where the log records are being written to)
Thanks, Gail. Are you suggesting then that there's no easy way to shrink this log back to its initial size? The reason I ask is because I'm particularly concerned about another database I have whose tlog has grown much, much larger than its original size and I'm trying to understand what I'm going to face and how to address it once I start tlog backups and try to shrink that file.
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
September 30, 2016 at 3:41 am
Mike Scalise (9/30/2016)
Are you suggesting then that there's no easy way to shrink this log back to its initial size?
No.
I said that an active VLF can't be moved. To shrink past an active VLF you need to make it inactive and you can't do that while it's the VLF that the transaction log records are being written to. So, make changes to the DB (eg create a table, insert rows, delete rows, repeat) so that the head of the log wraps around, then you can make the VLFs at the end of the file inactive with a log backups, then you can shrink the log file.
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
September 30, 2016 at 7:08 am
That all makes sense--thanks for explaining that. I think I'm almost there.
I made changes to the database up until the head of the log wrapped around (so at this point every record had a status of 2). I ran a LOG backup, then DBCC SHRINKFILE with a size of 500MB, and then DBCC LOGINFO again. Here are the first couple of rows.
RecoveryUnitIdFileIdFileSizeStartOffsetFSeqNoStatusParityCreateLSN
0265470464819252142640
02654704646547865651980640
That may be hard to read but it looks like the head of the log has wrapped around to the beginning, which was intended so that the inactive vlfs after that could be shrunk. When I run sp_helpfile or look at the OS level, I see that my log is 534MB. Definitely better than it was but still in excess of 500MB.
I don't mean to be a pain but is there an explanation for this? I would have expected just about 500MB for its size at this point...
Thanks,
Mike
Edit: Table formatting.
Mike Scalise, PMP
https://www.michaelscalise.com
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply