SMO ConnectionContext.StatementTimeout setting is ignored

Question

I am successfully using Powershell with SMO to backup most databases. However, I have several large databases in which I receive a "timeout" error "System.Data.SqlClient.SqlException: Timeout expired". The timout consistently occurs at 10 minutes. I have tried setting ConnectionContext.StatementTimeout to 0, 6000, and to [System.Int32]::MaxValue. The setting made no difference. I have found a number of Google references which indicate setting it to 0 makes it unlimited. No matter what I try, the timeouts consistently occur at 10 minutes. I even set Remote Query Timeout on the server to 0 (via Studio Manager) to no avail. Below is my SMO connection where I set the time out and the actual backup function. Further below is the output from my script.

UPDATE Interestingly enough, I wrote the backup function in C# using VS 2008 and the timeout override does work within that environment. I am in the process of incorporating that C# process into my Powershell Script until I can find out why the timeout override does not work with just Powershell. This is extremely annoying!

function New-SMOconnection {
    Param ($server, 
        $ApplicationName= "PowerShell SMO", 
        [int]$StatementTimeout = 0
    )
#    Write-Debug "Function: New-SMOconnection $server $connectionname $commandtimeout"
    if (test-path variable:\conn) {
        $conn.connectioncontext.disconnect()
    } else {
        $conn = New-Object('Microsoft.SqlServer.Management.Smo.Server') $server
    }
    $conn.connectioncontext.applicationName = $applicationName
    $conn.ConnectionContext.StatementTimeout = $StatementTimeout
    $conn.connectioncontext.Connect()
    $conn
}

$smo = New-SMOConnection -server $server
if ($smo.connectioncontext.isopen -eq $false) {
    Throw "Could not connect to server $($server)."
}

Function Backup-Database {
Param([string]$dbname)
$db = $smo.Databases.get_Item($dbname)
if (!$db) {"Database $dbname was not found"; Return}
$sqldir = $smo.Settings.BackupDirectory +  "\$($smo.name -replace ("\\", "$"))"
$s = ($server.Split('\'))[0]
$basedir = "\\$s\" + $($sqldir -replace (":", "$"))

$dt = get-date -format yyyyMMdd-HHmmss        
$dbbk = new-object ('Microsoft.SqlServer.Management.Smo.Backup')        
$dbbk.Action = 'Database'        
$dbbk.BackupSetDescription = "Full backup of " + $dbname        
$dbbk.BackupSetName = $dbname + " Backup"        
$dbbk.Database = $dbname        
$dbbk.MediaDescription = "Disk"  
$target = "$basedir\$dbname\FULL"
if (-not(Test-Path $target)) { New-Item $target -ItemType directory | Out-Null}
$device = "$sqldir\$dbname\FULL\" + $($server -replace("\\", "$")) + "_" + $dbname + "_FULL_" + $dt + ".bak"
$dbbk.Devices.AddDevice($device, 'File')
$dbbk.Initialize = $True
$dbbk.Incremental = $false
$dbbk.LogTruncation = [Microsoft.SqlServer.Management.Smo.BackupTruncateLogType]::Truncate
If (!$copyonly) { 
    If ($kill) {$smo.KillAllProcesses($dbname)}
    $dbbk.SqlBackupAsync($server) 
}
$dbbk

}

---

Started SQL backups for server LCFSQLxxx\SQLxxx at 05/06/2010 15:33:16
Statement TimeOut value set to 0.

DatabaseName    : OperationsManagerDW
StartBackupTime : 5/6/2010 3:33:16 PM
EndBackupTime   : 5/6/2010 3:43:17 PM
StartCopyTime   : 1/1/0001 12:00:00 AM
EndCopyTime     : 1/1/0001 12:00:00 AM
CopiedFiles     : 
Status          : Failed
ErrorMessage    : System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
                  The backup or restore was aborted.
                  10 percent processed.
                  20 percent processed.
                  30 percent processed.
                  40 percent processed.
                  50 percent processed.
                  60 percent processed.
                  70 percent processed.
                     at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
                     at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
                     at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
                     at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
                     at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
                     at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
                     at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
                     at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

Ended backups at 05/06/2010 15:43:23

Totten

Answer 1

"Slapping my head repeatedly and saying 'what was I thinking?'".

I created a new connection and used it to connect to the server. However, the actual backup statement uses $server (servername) instead of the $smo server object with the established connection. Consequently, the backup statement actually establishes a totally new connection devoid of the connection attribute to reset the default statement timeout.

Changing the backup statement to

$dbbk.SqlBackupAsync($smo)

fixes the problem.

Totten

I am using SqlBackup instead of SqlBakcupAsync but this tip still worked for me. I was passing the server name so a new connection was being created. After switching to pass the server object with ConnectionContext.StatementTimeout set to [Int32]::MaxValue I was able to take a backup without the command timing out. – Prostate

Answer 2

If you use SqlBackAsync how would you know if when the backup completes? I have the same problem as your original code... but my code has a function which depends on the backup to have completed successfully. So if I call async I need an operation to poll and check for the backup to complete.

Kowatch