sql-server – 忽略SMO ConnectionContext.StatementTimeout设置

前端之家收集整理的这篇文章主要介绍了sql-server – 忽略SMO ConnectionContext.StatementTimeout设置前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我成功地使用Powershell和SMO来备份大多数数据库.但是,我有几个大型数据库,我收到“超时”错误“System.Data.sqlClient.sqlException:Timeout expired”.时间一直发生在10分钟.我已经尝试将ConnectionContext.StatementTimeout设置为0,6000,并设置为[System.Int32] :: MaxValue.设置没有任何区别.我发现了许多Google引用,表明将其设置为0会使其无限制.无论我尝试什么,超时都会持续发生在10分钟.我甚至将服务器上的远程查询超时设置为0(通过Studio Manager)无济于事.下面是我的SMO连接,其中我设置了超时和实际的备份功能.下面是我脚本的输出.

UPDATE
有趣的是,我使用VS 2008在C#中编写了备份函数,并且超时覆盖在该环境中工作.我正在将C#进程合并到我的Powershell脚本中,直到我找到为什么超时覆盖不能仅用于Powershell.这非常烦人!

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

解决方法

“反复拍打我的头,说’我在想什么?’”.

我创建了一个新连接并用它连接到服务器.但是,实际的备份语句使用$server(servername)而不是具有已建立连接的$smo服务器对象.因此,备份语句实际上建立了一个全新的连接,没有连接属性来重置默认语句超时.

将备份语句更改为

$dbbk.sqlBackupAsync($smo)

解决了这个问题.

猜你在找的MsSQL相关文章