September 17, 2019 at 11:07 am
Hi everyone - first post 🙁
We have 3 MSSQL servers in 3 AG Groups. They would be SQL F, G and H. F is primary for AG3, G is primary for AG4 and H is primary for AG5. Each non primary server is secondary read only for each AG Group, so AG3 has G and H as read only secondaries.
We have 2 very large DB's in SQL F and I have Transaction logs backed up by SQL H and sent to a different server every 10 minutes, using a maintenance plan on SQL H. Each night, a full backup of the server databases is carried out also from SQL H
However, we never back up the server. These servers are in a virtual environment.
In days gone by, full backups of the server would truncate the logs, however, a full backup doesn't do this.
Currently, we do not keep transaction logs for more than a day, (since I thought all I would need is the full backup and tlog files from the past backup, which isn't true since I tried a test to do restore and it was asking for logs that I didn't have)
I have since tested a full backup post truncating the log and done a sucessful restore with tlogs.
The question is, do I script a truncating the logs just prior to running a full backup, so that we can keep the full backup and corresponding days transactional logs. If so, how would I script this as the only thing I have seen is about changing the database to simple mode, which just won't work with live databases in an AG Group.
Your help would be appreciated.
Regards
Stuart
September 17, 2019 at 12:17 pm
Hello,
When you say "I have Transaction logs backed up by SQL H ". Which method are you using for this? Log shipping?
Sorry I'm not sure of the cause of the issue but I think this question might help someone identify it.
Thanks
September 18, 2019 at 8:44 am
Ok - so it seems a command such as backup log <database> to disk='nul:' will sort this
it truncates the transaction log, so if I do that just before a full db backup, the following transaction logs will work along side that full backup until the next full backup where another truncate and full backup will be carried out.
September 18, 2019 at 6:16 pm
I think I'm correct in saying that it will sort it because it's running a log backup but it doesn't explain the cause of the original problem. As you probably know backing up to disk null means it's not actually stored anywhere.
September 19, 2019 at 3:37 pm
I'm slightly confused about your problem. Full backups never truncated the log. They include part of the log, but you've always needed a log backup to get file reuse and mark the records as free.
The recommendation is that you keep all logs since the oldest full backup you might restore. That way you can restore if there are file issues. Meaning, if I have this:
If I have an issue wed afternoon, and the Wed full backup file (deleted, corrupted, etc.), then I can go back to the Mon or Tue backups, restore the full+logs, and then apply Wed logs as needed. If I only have Wed logs, I can't go to the Tue full backup and somehow apply the Wed logs.
I think your message is confusing in that you're mentioning where you call the backups from. That's not relevant. Also, "back up the server" doesn't mean anything. Do you mean the Windows host + SQL stuff, do you mean the instance? There is no instance backup. There are database backups of system databases and user databases, or file level backups of binaries.
September 19, 2019 at 7:09 pm
Ok - so it seems a command such as backup log <database> to disk='nul:' will sort this
it truncates the transaction log, so if I do that just before a full db backup, the following transaction logs will work along side that full backup until the next full backup where another truncate and full backup will be carried out.
This is actually quite dangerous - as you are intentionally breaking the log chain and could prevent you from restoring your system from a disaster. For instance, if the latest backup file is corrupted you have no way of restoring your system to a current point in time. You cannot restore the previous good backup - and all transaction logs from that point forward...because this null backup.
Also - if your database becomes corrupted and you don't (or aren't able) to correct it immediately - then all backups taken after the corruption occurred will contain that corruption. You would need to be able to go back to a known good backup without the corruption - and restore all transaction logs from that point forward. With these breaks in the log chain...that is not possible.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 20, 2019 at 8:25 am
Yes - I re-read the OP and realize how it can be confusing. I had meant that in the past, a program such as backup exec would truncate the logs after a full backup of the server.
I also understand about the danger of truncating the logs. However, we only have space for 2 days worth of transaction logs, so there must come a time that I truncate these logs in order to be able to do a full restore and incorporate the transaction logs into a restore so we can bring it up to date... and, I guess, this is the crux of the matter.
If I do a full backup on Monday, and we have 2 days of transaction logs, theoretically, I can bring this up to Wed. However, if I have an issue on Thursday, if I restore tuesday or wednesday full backup and then try and restore the transaction logs, it asks me
Ok - the penny has just dropped - it should ask me for transaction logs from wednesday onwards to thursday... I need to test this as I am sure it asked for logs before this date, which it shouldn't.
September 23, 2019 at 2:30 pm
You never need to truncate the logs, but I think you get this now.
You can delete the log backups on Mon after Wed's full backup for space reasons, but the restore of the full always resets the log chain. Then you need logs from that point on.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply