Job Commands

Overview

The step command is the least visible code on an instance. It is not in a module, so Schema Search does not reach it. It is not in a file anybody has in source control. And the only way to read it in SSMS is to open each job and click through its steps one at a time.

This is the page somebody opens when they need to answer “does anything on this server still call that procedure”, and today that answer takes a cursor over sysjobsteps in a query window.

The Job Commands page: every step command on the instance in one list
The whole page in the Every step view. Fifty six steps across forty two jobs, 30.3kb of command text, and eight steps that run outside T-SQL.

Where to find it

Expand a server in the tree, expand the msdb database, then MSDB → Job Commands.

Shown only on an instance that can have SQL Server Agent.

What comes for free once it is one list

Steps that do not run as the job owner

Steps calling xp_cmdshell, running PowerShell or ActiveX, or shelling out with CmdExec. Those are worth knowing about whether or not anybody thinks they are a risk, because what they run as is decided by a proxy rather than by the job.

Steps naming a database that is not on this instance any more

Agent does not check. The step fails at run time with an error that names the database and nothing else, on whatever night it next runs.

The same command pasted into several jobs

Which is how an instance ends up with three copies of a cleanup routine and one of them fixed.

Steps writing an output file

That is the other place job output goes, and the path is usually on a drive nobody has looked at in years.

The three views

View What it shows
Every step All of them, in job and step order.
Outside T-SQL Only the steps whose subsystem is not T-SQL: CmdExec, PowerShell, ActiveX, SSIS, replication, analysis services.
Worth a look The steps this page has something to say about: a shell out, a missing database, a duplicated command, an output file on a path.

Reading the chart

The bars: steps by subsystem, which decides what account the step runs as
Forty eight of the fifty six steps are T-SQL with nothing flagged. The seven other bars are the subsystems that run outside SQL Server, and every one of them has a step worth looking at.

One bar per subsystem, because the subsystem is what decides the account a step runs as. The line under each name is how many of those steps this page has something to say about.

A T-SQL step runs in the job’s own context. Everything else falls back to a proxy or to the Agent service account, which is why the header counts those separately.

Reading the grid

The grid: job, step, subsystem, where it runs and what it runs as
Runs in is marked when that database is not on this instance any more, and Output file is the other place job output goes. Double-click a row to read the whole command.
Column What it is
Job The job.
Step The step name.
Subsystem T-SQL, CmdExec, PowerShell, SSIS, ActiveX and the rest.
Runs in The database the step is set to run in. Marked when that database is not on this instance.
Runs as The proxy’s credential, or the Agent service account where there is no proxy.
Output file Where the step writes its output, when it writes one.
Command The command itself.
Worth knowing What the page noticed about this step.

Double-click a row to read the whole command rather than the part the grid can show. The right-click menu copies it in full.

The toolbar

Control What it does
Every step / Outside T-SQL / Worth a look The three views.
Step failures Opens Job Step Failures.
Who owns them Opens Agent Security.

Nothing here runs anything

The commands are shown and never executed. This is a reading page for code that is already scheduled to execute itself.

Where the data comes from

Source What it gives
dbo.sysjobsteps The command, subsystem, database_name, proxy_id, output_file_name, on-success and on-failure actions.
dbo.sysjobs The job names and whether the job is enabled.
dbo.sysproxies, sys.credentials What each step runs as.
sys.databases Which of the named databases actually exist.

Messages you may see

There are no job steps to read. Either this instance runs no jobs, or this login cannot see the ones it has.

Report Why you would go there
Job Step Failures Which of these steps fails, and what it says when it does.
Agent Security The proxies and owners behind the Runs as column.
Maintenance Plans The steps that are a maintenance plan rather than a script.
Package Store The packages an SSIS step runs.
Schema Search The code that is in modules, which this page complements.

Frequently asked questions

Does anything still call this procedure? Search the Command column. Between this page and Schema Search, the scheduled code and the stored code are both covered.

A step runs in a database that does not exist and the job is green. Because the job has not run since the database was dropped, or that step is skipped by an on-success action further up. It will fail on its next real run.

Why call out PowerShell and CmdExec steps? Not as an accusation. Those steps leave the engine, so what they can do is decided by a Windows identity rather than by SQL Server permissions, and that identity is on Agent Security.

Can I edit a command from here? No. This page reads. Changing a job step belongs in SSMS or in a deployment script.