PowerShell variable assignment randomly fails

Question

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
}

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. — carlpett
– carlpett, Commented Apr 30, 2013 at 13:45
I would suggest sending along the $Error variable with your email as well, it might actually contain the reason — carlpett
– carlpett, Commented Apr 30, 2013 at 13:46
Good thoughts. A co-worker suggested adding a sleep between the assignment and the if statement so I've done that. I've also added the $error variable to the email. I bet you're correct about the lazy evaluation timing out the first time. I might try re-assigning the variable in the if statement to see if that fixes it. But I'll try that after I see if the change I just made proves useful. I will update in the next day or two. — Elijah W. Gagne
– Elijah W. Gagne, Commented Apr 30, 2013 at 14:51
That was quicker than expected. The sleep had no effect and the $error variable showed up in the email as $null. I've updated the posting to use your first suggestion. — Elijah W. Gagne
– Elijah W. Gagne, Commented Apr 30, 2013 at 16:02
Looks like the edit is working to solve the problem. I adjusted it to send me an email in both if statements. I'm only ever getting an email from the first if statement. So sometimes the variable assignment fails (presumably due to the lazy evaluation), but then it's getting catch and fixed by the if statement. Feel free to post something I can can accept as an answer. — Elijah W. Gagne
– Elijah W. Gagne, Commented May 1, 2013 at 12:57

carlpett · Accepted Answer · 2013-05-02 07:14:45Z

2

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

answered May 2, 2013 at 7:14

carlpett

12.7k5 gold badges52 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PowerShell variable assignment randomly fails

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related