PowerShell / SQL Server / ConnectionContext.StatementTimeout

Recently I had an issue with a database backup PowerShell script one of our customer using. The script was working fine until trying to do a backup of the large database. During backup attempts script starts failing with the following error:

Exception calling "SqlBackup" with "1" argument(s): "Backup failed for Server 'ServerName'. "
At line:67 char:1
+ $dbBackup.SqlBackup($s)
+ ~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : FailedOperationException
Exception calling "SqlBackup" with "1" argument(s): "Backup failed for Server 'ServerName'. "
At line:67 char:1
+ $dbBackup.SqlBackup($s)
+ ~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : FailedOperationException

It turned out, that the problem was connection timeout because the time needed to finish a backup of the large database. After passing 10 minutes backup operation timeouts.

I put the script log in place to confirm this.

08/31/2018 10:23 AM – Databases
08/31/2018 10:23 AM – Process DB01
08/31/2018 10:23 AM – StartBackup DB01
08/31/2018 10:23 AM – EndBackup DB01
08/31/2018 10:23 AM – Process DB02
08/31/2018 10:23 AM – StartBackup DB02
Exception calling “SqlBackup” with “1” argument(s): “Backup failed for Server ‘ServerName’. ”
At line:67 char:1
+ $dbBackup.SqlBackup($s)
+ ~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : FailedOperationException
Exception calling “SqlBackup” with “1” argument(s): “Backup failed for Server ‘ServerName’. ”
At line:67 char:1
+ $dbBackup.SqlBackup($s)
+ ~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : FailedOperationException
10/31/2018 10:33 AM – EndBackup DB02

Backup operation for DB02 started at 10:23AM and ended with an error at 10:33AM.

After adding command to prevent timeout monitoring error was gone:

$server.ConnectionContext.StatementTimeout = 0

Log after turning timeout off

08/31/2018 10:38 AM – Databases
08/31/2018 10:38 AM – Process DB01
08/31/2018 10:38 AM – StartBackup DB01
08/31/2018 10:38 AM – EndBackup DB01
08/31/2018 10:38 AM – Process DB02
08/31/2018 10:38 AM – StartBackup DB02
08/31/2018 11:12 AM – EndBackup DB02

Similar Posts:

• Powershell / Backup SQL Server DBs to Azure Storage with Powershell script
• Powershell / Use PowerShell to Backup all user databases to Azure Storage
• Log Shipping Issue / Operating system error 32 (The process cannot access the file because it is being used by another process)
• PowerShell / SQL Server Backup / Cannot find type Microsoft.SqlServer.Management.Smo.Backup
• SQL Server / Backup File Size Issue