PowerShell working with SQL Server Agent jobs

I’m going to demonstrate how simple is using PowerShell with SQL Server 2008 Provider and PSSnapins to work with SQL Server Agent jobs. I will cover the following areas:

1. Listing Jobs.

2. Starting jobs.

3. Monitoring Jobs.

You will become familiar with SQL Server 2008 PowerShell cmdlet “Invoke-SQLcmd” because this is the main cmdlet to make this work. I didn’t use any SMO components with these samples.

First, what is needed to run the following samples? Here’s some additional information:


## – requirement:

## – 1. Must have SQL Server 2008 in order the use the SQL Server provider and SQL PSSnapins.

## – 2. Use SQLPS.exe to run this script, or add the following code from mwories blog into

## your “WindowsPowerShell” folder in your User profile “My Document” folder. Here’s how:

## http://blogs.msdn.com/mwories/archive/2008/06/14/SQL2008_5F00_Powershell.aspx

## – 3. Results will be display on screen or add the code to output to a file. (" | Out-File …")


Second, here’s a brief description of the SQL Server 2008 PowerShell Cmdlet “Invoke-SQLcmd” copied from the PowerShell V2 help documentation using “Help Invoke-SQLcmd”: (it is self-explanatory)


Runs a script containing the languages and commands supported by the SQL Server sqlcmd utility. The languages supported are Transact-SQL and the XQuery syntax supported by the Database Engine.

Invoke-Sqlcmd also accepts many of the commands supported by sqlcmd, such as GO and QUIT. Invoke-Sqlcmd accepts the sqlcmd scripting variables, such as SQLCMDUSER. Invoke-Sqlcmd does not set sqlcmd scripting variables by default. Invoke-Sqlcmd does not support the sqlcmd commands primarily related to interactive script editing.

The commands not supported include : !!, :connect, :error, :out, :ed, :list, :listvar, :reset, :perftrace, and :serverlist.

The first result set the script returns is displayed as a formatted table. Result sets after the first are not displayed if their column list is different from the column list of the first result set. If result sets after the first set have the same column list, their rows are appended to the formatted table that contains the rows that were returned by the first result set.

Invoke-Sqlcmd does not return message output, such as the output of PRINT statements, unless you use the PowerShell -Verbose parameter.“

Third, from SQL Server, we are going to use the following T-SQL Stored-Procedures:

1. “sp_help_jobactivity” – This one will list all active and non-active jobs on the SQL Server.

2. “sp_start_job” – This procedure will start a SQL job.

Now, we are ready to start using PowerShell with our SQL Server jobs.


1 – Listing SQL Agent Jobs

Here I’m showing two ways to list SQL jobs and each one of the two procedures will give us very interesting results:

1. From the PowerShell console, navigate using “CD” to where the jobs are stored.

2. Use the Cmdlet “Invoke-SQLcmd” to execute the SQL Stored-Procedure “sp_help_jobactivity”.

With option #1, you are using the PSdrive “SQLServer\…” to navigate to the job and then will need to loop through the results to display both the Job and their job steps information.


$Srv = "mtrinidad-01\MSQL2K8R2B"
$SQLjob = $null ; $jobHist = $null ; $jobName = $null ; 
cd SQLSERVER:\SQL\$Srv\jobserver\jobs\
$SQLjob = dir
foreach($job in $SQLjob){
    $jobHist += $job | select Parent, name, lastRunDate, lastRunOutcome | ft -auto 
    foreach($jobName in $job){
        $jobFolder = "$jobName" + '.0\Jobsteps'
        cd SQLSERVER:\SQL\$Srv\jobserver\jobs\$jobFolder
        $jobHist += dir | select SubSystem, Parent, name, lastRunDate, lastRunOutcome | ft -auto 
    cd SQLSERVER:\SQL\$Srv\jobserver\jobs\
cd \
## - Results
## - This will give you a breakout both the Job and all their steps:


Now, with option #2, we use the SQL Stored-Procedure to get the information about the jobs using one line of code.


$SQLjobs = Invoke-SQLcmd -Server ‘mtrinidad-01\MSQL2K8R2B' -Database MSDB "exec sp_help_jobactivity”
# We use the Get-member to display only the $SQLjobs object properties
$SQLjobs | gm -MemberType Property



# - List all jobs in SQL Server Agent:
$SQLjobs | Select Job_Id, Job_Name, Run_Status, Stop_Execution_date | ft -Auto


## - select a SQL job:
$SQLjobs = Invoke-SQLcmd -Server ‘mtrinidad-01\MSQL2K8R2B' -Database MSDB "sp_help_jobactivity NULL, 'syspolicy_purge_history'"



2 – Start SQL Agent Jobs

This is a quick section because will be using the SQL Stored-Procedure “sp_start_job” to build our collection of .NET objects with one line.


$SQL = "sp_start_job 'syspolicy_purge_history' "
Invoke-SQLcmd -Server ‘mtrinidad-01\MSQL2K8R2B' -Database MSDB $SQL


This cmdlet will not return any information back to you. It will only start the job. So the next step is to monitor the running job.


3 – SQL Job Monitoring

Although, Listing and monitoring use the same Stored-Procedure “sp_help_jobactivity”, the only difference is that we are looking for the “status” value. When the job is running, the Job_Status will have a value of NULL, if the job fails the value is 0, and if the job ended successfully then the value is 1.


## - First time to get job_Status while is running...
$SQLjobs = Invoke-SQLcmd -Server ‘mtrinidad-01\MSQL2K8R2B' -Database MSDB "sp_help_jobactivity @job_id = NULL, @job_name = 'syspolicy_purge_history'"


Reminder: Any time you create a .NET PSobject using the “$variable = value or PowerShell cmdlet or …”, this object is static and not dynamic. So, you need to refresh that .NET PSobject by running again the same lines that execute the Stored-Procedure “sp_help_jobactivity”:

Example: (second time )

## - Second time to Refresh job_Status while is running...
$SQLjobs = Invoke-SQLcmd -Server ‘mtrinidad-01\MSQL2K8R2B' -Database MSDB "sp_help_jobactivity NULL, 'syspolicy_purge_history'"

See the results in the following image below. The first time running the two liner script, the dates and job_status statistics are set to NULL values. The second time running, the job completed successfully with dates showing and the job_status = 1.

Now, as you can possibly see, you can build this procedure to include:

1. Use the “ForEach” statements and loop through your SQL jobs.

2. Save the results of the jobs to a text file or you own table.

3. Build your own script SQL Job monitoring solution.

4. Execute/Start SQL jobs remotely from your desk.

This is just to name a few things.

Here’s some resource information I use for this blog:

· http://blogs.msdn.com/mwories/archive/2008/06/14/SQL2008_5F00_Powershell.aspx

· http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/3f95843c-f63e-41c3-b8cf-befc615e6b94/

Update PowerShell V1 to V2 on non-Window 7 and Windows 2008 R2 – Windows Management Framework (Windows PowerShell 2.0, WinRM 2.0, and BITS 4.0)


Happy PowerShell Scripting!!!


About maxt2posh

I’m from Puerto Rico, have been working with computers since 1979. Throughout many years, I have worked with SQL Server Technologies, provided support to Windows Servers/Client machines, Microsoft Virtualization Technologies, and build some Visual Studio solutions. I’m a Microsoft PowerShell MVP since 2009 and MVP SAPIEN Technologies since 2010. I speak in most of the SQLSaturday, IT Pro and .NET camps events around the Florida’s State. Also founder of the Florida PowerShell User Group which meeting every 3rd Tuesday evening of the month.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s