SMO ConnectionContext.StatementTimeout parameter ignored

Question

I successfully use Powershell with SMO to back up most databases. However, I have several large databases in which I get the error "timeout" "System.Data.SqlClient.SqlException: Timeout expired". The time interval sequentially occurs after 10 minutes. I tried setting ConnectionContext.StatementTimeout to 0, 6000 and [System.Int32] :: MaxValue. The setting did not matter. I found several google links that indicate that setting it to 0 makes it unlimited. No matter what I try, timeouts occur sequentially after 10 minutes. I even set the remote request timeout on the server to 0 (via Studio Manager) to no avail. Below is my SMO connection, where I set the timeout and the actual backup function.The following is the output from my script.

UPDATE Interestingly, I wrote a backup function in C # using VS 2008, and timeout redefinition works in this environment. I'm in the process of including this C # process in my Powershell Script until I can figure out why timeout redefinition doesn't work only with Powershell. This is very 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

Answer 1

"Clapping her head several times and saying," What was I thinking? "

. $server ( ) smo . , , , reset - .

$dbbk.SqlBackupAsync($smo)

.

Answer 2

SqlBackAsync, , ? , ... , , . , async, .