When you're using the Use-DlmDatabaseRelease PowerShell cmdlet, you see the following error text or similar:

WARNING: Error 'Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.' 
...
+ FullyQualifiedErrorId : SqlScriptExecutionError,RedGate.SQLRelease.PowerShell.Commands.UseDatabaseReleaseCommand

Before following the instructions on this troubleshooting page, check the error log includes both the lines shown in the example above. This shows the timeout error is associated with the Use-DlmDatabaseRelease PowerShell cmdlet.

You might see this error if you're using one of the following three Octopus Deploy step templates:

• Redgate - Deploy from Database
• Redgate - Deploy from Database Release
• Redgate - Deploy from Package

Cause

One of the batches in the update script took longer to execute than the execution timeout duration (set by default to 30 seconds).

Fix

Increase the duration of the execution timeout by adding the -QueryBatchTimeout parameter to the Use-DlmDatabaseRelease cmdlet.

The value of the -QueryBatchTimeout is in seconds. A value of zero indicates no timeout.

If you're using one of the Octopus Deploy step templates mentioned above, versions released on or after July 8th contain a query batch timeout parameter. Use this to specifying a significantly longer duration than the 30 second default. If you're using an older version of any of these templates, you'll need to edit the line in the template that contains the Use-DatabaseRelease cmdlet (this is the previous name for the Use-DlmDatabaseRelease cmdlet).

Using PowerShell cmdlets

In the Use-DatabaseRelease cmdlet, add the-QueryBatchTimeout parameter, specifying a significantly longer duration than the 30 second default:

$release | Use-DlmDatabaseRelease -DeployTo $targetDB -Verbose -QueryBatchTimeout 120

If the error continues when you run the cmdlet, you may need to increase the duration of the timeout further. You can see how long each batch takes to execute by adding the -Debug parameter:

$release | Use-DlmDatabaseRelease -DeployTo $targetDB -Verbose -QueryBatchTimeout 120 -Debug

Using Octopus Deploy step templates

In SQL Release 1.2.1.3062, a query batch timeout parameter was added to the following Octopus Deploy step templates: 

• Redgate - Deploy from Database
• Redgate - Deploy from Database Release
• Redgate - Deploy from Package

These versions of the templates were published to the Octopus Deploy library on July 8th, 2015.

If you're using an earlier version of any of these template, the query batch timeout parameter won't be available. Instead, you can edit your existing template and add a query batch timeout parameter:

1. In the header of Octopus Deploy, click Library. 
2. Go to the Step templates tab.
3. Click the Redgate Deploy step that's causing the error. Unless the step has been renamed, this will be one of the following:
   Redgate - Deploy from Database
   Redgate - Deploy from Database Release
   Redgate - Deploy from Package

4. Near the bottom of the PowerShell script, find the line that contains the Use-DatabaseRelease cmdlet. Add the -QueryBatchTimeout 120 parameter and the -Debug parameter:

   Example

   ...
   # Deploy the schema change
   $release | Use-DatabaseRelease -DeployTo $targetDB -Verbose -QueryBatchTimeout 120 -Debug
   ...

5. Click Save.

You also need to update the project step:

1. Click Projects and select the project you need to update.
2. Go to the Process tab and select the step based on the template you edited earlier.
3. Click Update.

If the error continues, you may need to increase the duration of the timeout further.