SQL Server Agent Job and Powershell Version

Since SQL Server 2008, we are able to create SQL Agent job step to run Windows PowerShell scripts. That is great! However, as the latest version 3.0 release of Windows PowerShell, be aware that SQL Server Agent may not be running the same PowerShell version. Before you deploy any PowerShell script to SQL Agent job, you need to make sure you test the PowerShell scripts in the right version.

There are 2 ways of running PowerShell scripts in SQL Server Agent job. It can be run in,

1) PowerShell job step
2) Command prompt (CmdExec) job step

If the Windows PowerShell scripts is being run as PowerShell job step, SQL Server Agent job step run sqlps utility to launches PowerShell and load SQL Server PowerShell module (also called sqlps). The gotcha is that it launches its own PowerShell version regardless of the latest version of PowerShell installed on the server.

On SQL Server 2012, sqlps utility launches PowerShell 2.0
On SQL Server 2008/2008R2, sqlps utility launches PowerShell 1.0

If you have downloaded and installed the latest PowerShell 3.0 or currently on Windows Server 2012 or Windows 8 (pre-installed with PowerShell 3.0), you are likely to have developed your scripts in PowerShell 3.0. There are difference between PowerShell 2.0 and PowerShell 3.0, and your script written and tested in PowerShell 3.0 may not work in PowerShell 2.0 environment. (well, that what happen to me)

If you have installed PowerShell 3.0, there is no easy way to switch Windows PowerShell Integrated Scripting Environment (PowerShell ISE) to previous version. However, you could easily change version in Windows PowerShell (powershell.exe) with -version command,

powershell -version 2

At least you can still verify the newly developed script this way if it works on older version. Of course you can always uninstall the latest version and install the PowerShell ISE 2.0 for your development and test.

However, in certain circumstances that you really need to use PowerShell 3.0, you could choose the second way of running PowerShell script, through CmdExec job step. SQL Server Agent uses command prompt to run PowerShell (the installed version, e.g. PowerShell 3.0) and executes the PowerShell scripts.

A quick verification on SQL Agent job step running PowerShell version,

- Create a SQL Agent job
- Create first job step, with step type change to PowerShell
- Insert the command $psversiontable.psversion to retrieve the executing powershell version

- On advanced tab, check 'Include step output in history' to return version result in the job history for review.

- Create second job step, with step type change to Operating System (CmdExec)
- Insert the command "C:\Windows\system32\WindowsPowerShell\v1.0\powershell.exe" "$psversiontable.psversion" to retrieve the executing powershell version. On advanced tab, check 'Include step output in history' to return version result in the job history for review.

- Save the job
- Run the job

In my environment with Windows Server 2012 and SQL Server 2012 installed, the job history result shows version 2 is returned on PowerShell step and version 3 is returned in CmdExec step.

Refer to this blog post, PowerShell and Performance Monitor (Perfmon) Counter for PowerShell script to extract performance counter and import into database for analysis.

1 comment:

  1. A nice quick way to run modern Powershell with old SQL PowerShell tool: Declare a string full of modern PS script and then pass it into the server's modern "powershell".


    $psCmd = '

    $TRNRetentionDate = (Get-Date).AddDays(-30);
    $BAKRetentionDate = (Get-Date).AddDays(-30);

    Get-ChildItem -Recurse -Path "\\MyUNCPath\WontWorkInSQLPS\SQLBackups" |
    Where {$_.LastWriteTime -LT $TRNRetentionDate -AND $_.PSIsContainer -Eq $False -AND $_.Extension -IN @(".bak",".trn")} |

    if($_.Extension -EQ ".bak" -AND $_.LastWriteTime -LT $BAKRetentionDate){
    $_ | Remove-Item

    if($_.Extension -EQ ".trn" -AND $_.LastWriteTime -LT $TRNRetentionDate){
    $_ | Remove-Item



    echo $psCmd | powershell