"SQL - Backup Database" timed out for a big database

Legacy Uncategorised
1

Hi guys.

[Version: Octopus 3.3.12]

I am using a pre-configured Step Template (SQL - Backup Database) available from Step templates section.

When backing up a big database (65+ GB), I noticed that it always fails with a SqlClient operation timeout as shown below:

BackupDatabase : An error occurred backing up the database!
System.Management.Automation.MethodInvocationException: Exception calling “SqlB
ackup” with “1” argument(s): "Backup failed for Server ‘XYZ’. " —>
Microsoft.SqlServer.Management.Smo.FailedOperationException: Backup failed fo
r Server ‘XYZ’. —> Microsoft.SqlServer.Management.Common.Executi
onFailureException: An exception occurred while executing a Transact-SQL statem
ent or batch. —> System.Data.SqlClient.SqlException: Timeout expired. The ti
meout period elapsed prior to completion of the operation or the server is not
responding.
at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(Execu
teTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchExcep
tion)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(St
ring sqlCommand, ExecutionTypes executionType)
— End of inner exception stack trace —
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(St
ring sqlCommand, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(St
ringCollection sqlCommands, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(Strin
gCollection queries)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQueryWithMe
ssage(StringCollection queries, ServerMessageEventHandler dbccMessageHandler, B
oolean errorsAsMessages)
at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server se
rver, StringCollection queries)
at Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv)
— End of inner exception stack trace —
at Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv)
at SqlBackup(Object , Object )
at System.Management.Automation.DotNetAdapter.AuxiliaryMethodInvoke(Object t
arget, Object arguments, MethodInformation methodInformation, Object origin
alArguments)
— End of inner exception stack trace —
at System.Management.Automation.DotNetAdapter.AuxiliaryMethodInvoke(Object t
arget, Object arguments, MethodInformation methodInformation, Object origin
alArguments)
at System.Management.Automation.DotNetAdapter.MethodInvokeDotNet(String meth
odName, Object target, MethodInformation methodInformation, Object argument
s)
at System.Management.Automation.Adapter.BaseMethodInvoke(PSMethod method, Ob
ject arguments)
at System.Management.Automation.ParserOps.CallMethod(Token token, Object tar
get, String methodName, Object paramArray, Boolean callStatic, Object valueTo
Set)
at System.Management.Automation.MethodCallNode.InvokeMethod(Object target, O
bject arguments, Object value)
at System.Management.Automation.MethodCallNode.Execute(Array input, Pipe out
putPipe, ExecutionContext context)
at System.Management.Automation.ParseTreeNode.Execute(Array input, Pipe outp
utPipe, ArrayList& resultList, ExecutionContext context)
at System.Management.Automation.StatementListNode.ExecuteStatement(ParseTree
Node statement, Array input, Pipe outputPipe, ArrayList& resultList, ExecutionC
ontext context)
At C:\Octopus\Work\20160818070349-963\Script.ps1:128 char:15

  • BackupDatabase <<<< $DatabaseName $Devices $CompressionOption
    • CategoryInfo : NotSpecified: (:slight_smile: [Write-Error], WriteErrorExcep
      tion
    • FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorExceptio
      n,BackupDatabase
      The remote script failed with exit code 1

For your information, I am uncertain whether this is related to a bug in Octopus 3+ or to the database whose size has recently grown up just large enough to trigger this problem.

If anyone has experienced this, please kindly share your solutions/suggestions here.

Thank you.

Regards,

Sung-Jae

2

Hi Sung-jae,

Thank you for reaching out! This error is likely due purely to the timeout value hitting the limit before the task could complete:

The ti meout period elapsed prior to completion of the operation or the server is not responding.

Try increasing the timeout value (set to 36000 by default), and that should do the trick! You can do that in your Library -> Step Templates -> SQL - Backup Database (attached image 1)

And change the parameter “Connection Timeout” and change “Default value” (attached image 2)

Let me know how you go!

Kenny

1-StepTemplate.png
1-StepTemplate.png3840×2072 352 KB

2-ParameterChange.png
2-ParameterChange.png3840×2072 379 KB

3

Thanks Kenny.

Noticed I was using an old version of the Site Template, which was missing a number of parameters.

Your solution works after upgrading it and assigning a bigger number to Connection Timeout.

Thank you again.

Regards,

Sung-Jae