0

I have a serverless azure sql server instance with about 50 databases and I am trying to get the @databasesinparallel parameter to work to reindex all databases at the same time rather than one at a time. I get error The value for the parameter @DatabasesInParallel is not supported. Does this not work for azure serverless instances?

2
  • 3
    You can figure this out really easily by opening Ola's script and searching for the @DatabasesInParallel parameter. Jun 27, 2022 at 21:10
  • Not sure you can do cross database calls in Azure anyway Jun 27, 2022 at 22:44

1 Answer 1

2

The relevant code section from the IndexOptimize procedure shows there are two situations that can cause that error:

  IF @DatabasesInParallel NOT IN('Y','N') OR @DatabasesInParallel IS NULL
  BEGIN
    INSERT INTO @Errors ([Message], Severity, [State])
    SELECT 'The value for the parameter @DatabasesInParallel is not supported.', 16, 1
  END

  IF @DatabasesInParallel = 'Y' AND SERVERPROPERTY('EngineEdition') = 5
  BEGIN
    INSERT INTO @Errors ([Message], Severity, [State])
    SELECT 'The value for the parameter @DatabasesInParallel is not supported.', 16, 2
  END

Assuming you're passing in 'Y' for the parameter value, the reason for the error is that SERVERPEROPERTY('EngineEdition') is returning 5, which translates to Azure SQL Database.

Azure SQL Database does not support cross-database queries, so when automating Ola Hallengrens procs in Azure SQL DBs, the objects must be created in all target DBs and are executed individually. If you want to run the reindexing in parallel, I would suggest using Azure Elastic Jobs as these run in parallel across all target databases.

There are some limitations, they don't work with Azure SQL Databases using Private Endpoints for example, but for the most part, Elastic Jobs can perform the reindexing jobs just fine.

Some other alternatives:

  • Use Azure Automation and PowerShell Workflow to execute IndexOptimize. Workflows can be run in parallel.
  • Use a Logic App to execute IndexOptimize in parallel branches across your Azure SQL Databases.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.