sandeepiii avatar image
sandeepiii asked

sql server SSAS Powershell

Hi, Due to server idle time setting to 100mintes.My connection gets disconnected and cube processing gets interuptted. or stopped. How can i overcome this may set some connection property so tht it does not go idle and the connection is not disconnected.My job takes 3hrs to complete. param($ServerName="W96847\INST103", $DBName="RHRH_MFI", $ProcessTypeDim="ProcessFull",$ProcessTypeMG="ProcessFull", $Transactional="Y", $Parallel="Y",$MaxParallel=2,$MaxCmdPerBatch=5, $PrintCmd="N") ## Add the AMO namespace $loadInfo = [Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") [Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") [Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") if ($Transactional -eq "Y") {$TransactionalB=$true} else {$TransactionalB=$false} if ($Parallel -eq "Y") {$ParallelB=$true} else {$ParallelB=$false} $server = New-Object Microsoft.AnalysisServices.Server $server.connect($ServerName) if ($ -eq $null) { Write-Output ("Server '{0}' not found" -f $ServerName) break } $DB = $server.Databases.FindByName($DBName) if ($DB -eq $null) { Write-Output ("Database '{0}' not found" -f $DBName) break } Write-Output("Load start time {0}" -f (Get-Date -uformat "%H:%M:%S") ) Write-Output("----------------------------------------------------------------") Write-Output("Server : {0}" -f $Server.Name) Write-Output("Database: {0}" -f $DB.Name) Write-Output("DB State: {0}" -f $DB.State) Write-Output("DB Size : {0}MB" -f ($DB.EstimatedSize/1024/1024).ToString("#,##0")) Write-Output("----------------------------------------------------------------") Write-Output("DB processing started. Time: {0}" -f (Get-Date -uformat "%H:%M:%S")) $server.CaptureXml=$TRUE # Just capture server statements, dont execute them #Process dimensions foreach ($dim in $DB.Dimensions) { $dim.Process($ProcessTypeDim) } # Dimensions #Process cubes foreach ($cube in $DB.Cubes) { foreach ($mg in $cube.MeasureGroups) { foreach ($part in $mg.Partitions) { $part.Process($ProcessTypeMG) } } } # Separate step to process all linked measure groups. Linke MG does not have partitions foreach ($cube in $DB.Cubes) { foreach ($mg in $cube.MeasureGroups) { if ($mg.IsLinked) { $mg.Process($ProcessTypeMG) } } } $server.CaptureXML = $FALSE # Finish capturing statements. All statements are in Server.CaptureLog $cmdBatch = @" ##ProcessCmd## "@ $cmdBatch = $cmdBatch -replace("##MaxParallel##",$MaxParallel) #$ErrorActionPreference = "SilentlyContinue" $currentCmdNo=0; $currentCmdInBatchNo=0; $processCmd="";$currentBatchNo=0 $TotalCmdCount = $Server.CaptureLog.Count foreach ($cmdLine in $Server.CaptureLog) { $currentCmdNo = $currentCmdNo + 1 $processCmd = $processCmd + $cmdLine + "`n" $currentCmdInBatchNo=$currentCmdInBatchNo + 1 if ($currentCmdInBatchNo -ge $MaxCmdPerBatch -or $currentCmdNo -eq $TotalCmdCount) { #MaxCmdPerBatch reached, execute commands $processCmd = $cmdBatch -replace("##ProcessCmd##", $processCmd) if ($PrintCmd -eq "Y") { Write-Output($processCmd) } $currentBatchNo = $currentBatchNo + 1; Write-Output("=== Starting batch No {0}. Time: {1} ..." -f $currentBatchNo, (Get-Date -uformat "%H:%M:%S")) $Result = $Server.Execute($processCmd) # Report errors and warnings foreach ($res in $Result) { foreach ($msg in $res.Messages) { if ($msg.Description -ne $null) {Write-Output("{0}" -f $msg.Description)} } } # Reset temp values $processCmd = ""; $currentCmdInBatchNo=0; } }#foreach Write-Output("----------------------------------------------------------------") Write-Output("DB processing completed. Time: {0}" -f (Get-Date -uformat "%H:%M:%S")) Write-Output("----------------------------------------------------------------") Write-Output("Listing objects that are not processed")
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.