September 12, 2016 at 1:28 am
Dear all,
It seems that I need to change my logs backps from 1h (occur every one hour) to half an hour as the logs are getting bigger.
Can I have any negative impacted due to this in terms of performance? Lock, block, etc..?
Thanks a lot
September 12, 2016 at 2:06 am
No, backups don't take any locks on tables. There'll be a small IO penalty, but I'd be surprised if you even notice that. The frequency of your backups, however, should be determined more by your recoverability requirements than by the size of the log files.
John
September 12, 2016 at 3:57 am
IF the logs are getting bigger you have more, or longer running, transactions. Log backups can only remove from the log those transactions that are complete. If you have a higher volume of transactions or if your code is holding transactions open, you won't be able to clear out enough transactions during the backup process.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 12, 2016 at 3:59 am
John Mitchell-245523 (9/12/2016)
No, backups don't take any locks on tables. There'll be a small IO penalty, but I'd be surprised if you even notice that. The frequency of your backups, however, should be determined more by your recoverability requirements than by the size of the log files.John
I'd push back against this a little. I agree that the primary purpose for the log backup is meeting the Recovery Point Objective (RPO) that you work out with the business. However, there's nothing wrong with taking log backups more frequently as a means of keeping the log files themselves smaller. Since the transactions backed up are going to be the same regardless, you're only adding the number of files you maintain and a little bit of header information on each file to the stuff you have to maintain.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply