CODEWITHSUNDEEP
sqlpowershell
Elijah W. Gagne
Elijah W. Gagne

Reputation: 2841

PowerShell variable assignment randomly fails

I have a strange issue that only occurs roughly 1 out of 100 times. I have a PowerShell script using SQL's SMO library to do database backups. Here's a snip of the relevant code I'm using to troubleshoot the issue:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | out-null
$now = get-date
########################################
$server = new-object Microsoft.SqlServer.Management.Smo.Server
$server.ConnectionContext.StatementTimeout = 86400 # Allow backups to take up to 24 hours per database
$databases = $server.Databases
$script:totalsteps = $databases.count
########################################
if($script:totalsteps -eq $null -or $script:totalsteps -eq 0) {
    $body = "Backup start:" +  $now.tostring("yyyy-MM-dd hh:mm:ss tt") + "`r`n" + 
        "Error: " + (get-date).tostring("yyyy-MM-dd hh:mm:ss tt") + "`r`n" + 
        "`$script:totalsteps: " + $script:totalsteps + "`r`n" + 
        "`$databases.count: " + $databases.count + "`r`n" + 
        "`$databases: " + $databases + "`r`n" + 
        "`$server: " + $server
    send-mailmessage -from "[email protected]" -to "[email protected]" -subject "Server Null: $server" -smtpserver "mail.example.com" -body $body
}

The problem is that once in a while the if statement is evaluated true and I get an email that looks like:

Backup start: 2013-04-30 07:50:58 AM
Error: 2013-04-30 08:02:19 AM
$script:totalsteps: 
$databases.count: 4
$databases: [master] [model] [msdb] [tempdb]
$server: [serverA]

Of note is that the script start time vs error time is roughly 11 minutes which is somewhat strange. My only guess right now is that the server is under a lot of stress and so PowerShell silently fails the variable assignment and it moves on.

99 out of 100 times the if statement is false and I don't get an email. I can't understand why $script:totalsteps assignment isn't working 100% of the time. Any ideas? Anything else I can do to try to troubleshoot this?

UPDATE

In order to test the lazy evaluation theory, I've changed the code to be:

System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | out-null
$now = get-date
########################################
$server = new-object Microsoft.SqlServer.Management.Smo.Server
$server.ConnectionContext.StatementTimeout = 86400 # Allow backups to take up to 24 hours per database
$databases = $server.Databases
$script:totalsteps = $databases.count
############ NEW NEW NEW NEW ###########
if($script:totalsteps -eq $null -or $script:totalsteps -eq 0)
{
    $script:totalsteps = $databases.count * 4
    send-mailmessage -from "[email protected]" -to "[email protected]" -subject "Server Null: $server" -smtpserver "mail.example.com" -body "FIRST ATTEMPT"
}
########################################
if($script:totalsteps -eq $null -or $script:totalsteps -eq 0)
{
    $body = "Backup start:" +  $now.tostring("yyyy-MM-dd hh:mm:ss tt") + "`r`n" + 
        "Error: " + (get-date).tostring("yyyy-MM-dd hh:mm:ss tt") + "`r`n" + 
        "`$script:totalsteps: " + $script:totalsteps + "`r`n" + 
        "`$databases.count: " + $databases.count + "`r`n" + 
        "`$databases: " + $databases + "`r`n" + 
        "`$server: " + $server
    send-mailmessage -from "[email protected]" -to "[email protected]" -subject "Server Null: $server" -smtpserver "mail.example.com" -body $body
}

Upvotes: 2

Views: 333

Answers (1)

carlpett
carlpett

Reputation: 12613

Just guessing here, but since the assignment is the first access on the $databases variable: I believe that evaluation of these are lazy, so you may get a timeout when it tries to enumerate the databases because it first needs to open a connection. After that, you have already fetched the data, so it is available when you use it the second time. Bit hard to verify, though.

I would suggest sending along the $Error variable with your email as well, it might actually contain the reason

Upvotes: 2

Related Questions