Maintenance – Troubleshooting Ola Hallengren’s @DatabasesInParallel Parameter

maintenanceola-hallengren

I am testing out this new parameter @DatabasesInParallel and it seems to only back up 1 databases at a time but it doesn't specificy this as a limitation in the documentation so I am wondering if I might be doing something wrong.

I have 4 DBs set up and I am manually running the following 4 commands:

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'AdminDB',
@Directory = N'\\499bsl01\sqldata_test$',
@BackupType = 'FULL',
@Verify = 'Y',
@CleanupTime = 24,
@Compress = 'Y',
@CheckSum = 'Y',
@DatabasesInParallel = 'Y',
@LogToTable = 'Y',
@Execute = 'Y'

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'Pop',
@Directory = N'\\499bsl01\sqldata_test$',
@BackupType = 'FULL',
@Verify = 'Y',
@CleanupTime = 24,
@Compress = 'Y',
@CheckSum = 'Y',
@DatabasesInParallel = 'Y',
@LogToTable = 'Y',
@Execute = 'Y'

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'DaveTest_StackOverflow2010',
@Directory = N'\\499bsl01\sqldata_test$',
@BackupType = 'FULL',
@Verify = 'Y',
@CleanupTime = 24,
@Compress = 'Y',
@CheckSum = 'Y',
@DatabasesInParallel = 'Y',
@LogToTable = 'Y',
@Execute = 'Y'

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'DaveTest_SUPERUSER',
@Directory = N'\\499bsl01\sqldata_test$',
@BackupType = 'FULL',
@Verify = 'Y',
@CleanupTime = 24,
@Compress = 'Y',
@CheckSum = 'Y',
@DatabasesInParallel = 'Y',
@LogToTable = 'Y',
@Execute = 'Y'

When I check the QueueDatabase table, I can see that the DatabaseOrder and RequestStartTime column are the exact same for all 4 databases. However, when you look at the column DatabaseStartTime it shows the first 2 databases starting at the same time and then the next 2 databases dont start until the 2nd database has finished doing its backup and verify check.

QueueDatabase Table Results

Is this the intended functionality with the parameters that I am using? I would expect all 4 databases to be started at the exact same time with this parameter.

EDIT 11/14: Updated title/wording of post

Original post said it was backing up only 2 databases at a time but it was really only doing 1 database at a time. The first DB that is backed up in my test is so small that I didnt realize it finishes almost immediately and the second on starts. However you can see this

Best Answer

After reading Ola's last comment I had a light bulb moment and figured what I was doing wrong and got it working.

Since I was just testing these commands out I never actually put them into an Agent job - I figured they would work the same way by just manually executing them at the same time. I just had all 4 of the job commands pasted in a Query window and would just execute that. I thought this would work but it appears that it doesn't even though they all showed the same RequestStartTime in the QueueDatabase table.

Once I created the jobs and set a single schedule I could see the RequestStartTime varies by milliseconds but the DatabaseStartTime is the same for all DBs.

The screenshot below shows the QueueDatabase table from my test today (pink) which was successful when I created an Agent job. The test on 11/8 (blue) was when I manually executed the commands in a Query window.

Comparison

I asked Ola in an email if this functionality makes sense that it doesn't work when executing manually in a query window and below is his response:

When you execute all the commands in one query window, you can never get parallel executions (as T-SQL does not have support for that).

If you would like to test it in SSMS, then you need to us 4 different query windows, and then quickly start the execution of all of them.

Related Question