patternsqlMinor
Transaction Log Backups Serial or Parallel?
Viewed 0 times
backupslogserialparalleltransaction
Problem
We happen to be using SQL Server 2012 Standard Edition. I also happen to use Ola Hallengren's scripts to provide an easy, more flexible framework for doing backups and maintenance.
This question isn't so much about Ola's scripts as they are about a best practice. I realize the ultimate answer is "it depends on your company's requirements". But I am trying to seek the community's advice on how best to fulfill what I understand of our company's requirements.
I wish to set up transaction log backups for every 15 minutes. This way we hopefully lose no more than 15 minutes of data. Should I set up one job that uses ALL_DATABASES? or is it better to set up one job for each database and kick them all off in parallel? I ask, because I have the feeling based on how I see Ola's script functioning that the backups are kicked off in serial. The downside of serial would be that each successive backup waits until the other completes. This could potentially increase the amount of time between backups (ie, greater than 15 minutes). Plus my concern would be that a failure in one backup stops the others from happening, and I wouldn't want that to be the case. I would want the others to continue backing up.
So is it true that Ola's scripts execute in serial and also a failure stops successive backups?
And is it better to have a job for each database? or a single job that does all? My inclination is toward separate jobs, but I wish to understand what SQL Server DBA's in general tend to do.
This question isn't so much about Ola's scripts as they are about a best practice. I realize the ultimate answer is "it depends on your company's requirements". But I am trying to seek the community's advice on how best to fulfill what I understand of our company's requirements.
I wish to set up transaction log backups for every 15 minutes. This way we hopefully lose no more than 15 minutes of data. Should I set up one job that uses ALL_DATABASES? or is it better to set up one job for each database and kick them all off in parallel? I ask, because I have the feeling based on how I see Ola's script functioning that the backups are kicked off in serial. The downside of serial would be that each successive backup waits until the other completes. This could potentially increase the amount of time between backups (ie, greater than 15 minutes). Plus my concern would be that a failure in one backup stops the others from happening, and I wouldn't want that to be the case. I would want the others to continue backing up.
So is it true that Ola's scripts execute in serial and also a failure stops successive backups?
And is it better to have a job for each database? or a single job that does all? My inclination is toward separate jobs, but I wish to understand what SQL Server DBA's in general tend to do.
Solution
Should I set up one job that uses ALL_DATABASES? or is it better to set up one job for each database and kick them all off in parallel?
I would suggest to setup one job that would backup the transaction logs (serially). This would also make sure backup does not heavily utilities the I/O because you are running backup for database one at a time.
What could be possible drawbacks with running in parallel
-
Suppose you have 50 databases and you schedule transaction log backup of all the databases and they all start running in parallel this is definitely going to utilize lot of I/O. And if disk on which it is backing up files happen to have other data files you would see slowness. I have seen backup becoming slow when a poor query requesting lot of I/O runs along with backup job.
-
Again suppose you have 50 database would it not be difficult to manage 50 jobs in SQL Server agent and what would be condition if you have 100-200 databases I would just not like it when you open the SQL Server agent and see lots of job, just keep it simple. I am sure the same case would be with you.
The downside of serial would be that each successive backup waits until the other completes. This could potentially increase the amount of time between backups (ie, greater than 15 minutes).
Transaction log backups are mostly small and if you have a busy database producing lot of log records you might need to change the backup frequency. Mostly I have seen transaction log backup completing fine when frequency is 15 mins. I don't think should be matter of concern for you.
Plus my concern would be that a failure in one backup stops the others from happening, and I wouldn't want that to be the case
I would say just don't worry about it. Transaction log backups just cannot fail unless you made some mistake. The mistakes can be
-
The owner running the job is removed from AD
-
Someone changed the recovery model of database.
-
Insufficient disk space
Apart from above I have not seen any reason for transaction log backup to fail. Its very robust you can rely on it.
I would suggest to setup one job that would backup the transaction logs (serially). This would also make sure backup does not heavily utilities the I/O because you are running backup for database one at a time.
What could be possible drawbacks with running in parallel
-
Suppose you have 50 databases and you schedule transaction log backup of all the databases and they all start running in parallel this is definitely going to utilize lot of I/O. And if disk on which it is backing up files happen to have other data files you would see slowness. I have seen backup becoming slow when a poor query requesting lot of I/O runs along with backup job.
-
Again suppose you have 50 database would it not be difficult to manage 50 jobs in SQL Server agent and what would be condition if you have 100-200 databases I would just not like it when you open the SQL Server agent and see lots of job, just keep it simple. I am sure the same case would be with you.
The downside of serial would be that each successive backup waits until the other completes. This could potentially increase the amount of time between backups (ie, greater than 15 minutes).
Transaction log backups are mostly small and if you have a busy database producing lot of log records you might need to change the backup frequency. Mostly I have seen transaction log backup completing fine when frequency is 15 mins. I don't think should be matter of concern for you.
Plus my concern would be that a failure in one backup stops the others from happening, and I wouldn't want that to be the case
I would say just don't worry about it. Transaction log backups just cannot fail unless you made some mistake. The mistakes can be
-
The owner running the job is removed from AD
-
Someone changed the recovery model of database.
-
Insufficient disk space
Apart from above I have not seen any reason for transaction log backup to fail. Its very robust you can rely on it.
Context
StackExchange Database Administrators Q#128424, answer score: 6
Revisions (0)
No revisions yet.