This post contains a SQL script that will return a list of all SQL Server Agent Jobs on a Microsoft SQL Server instance.
The sysjobs and sysjobsteps tables we are querying are in the M-S-D-B database. Interesting fact, I am unable to publish a post with this actual database name without seeing a JSON error.
When querying system tables in MS SQL Server, we should refer to Microsoft Docs as linked ^ MS Docs gives us a reference point for column definitions and the docs often include various example SQL code snippets.
List all SQL Agent jobs in SQL Server
As mentioned above we’re querying the M S D B database. The following script will return a list of all the SQL Agent jobs on the SQL Server host –
script to get sql agent jobs
This SQL script returns a row for each job step contained within an Agent job, including information such as whether the job is enabled/disabled, and the TSQL command text for the job step.
If you want to return only Agent Jobs, not including Job Steps, you can query the sysjobs system table on its own (SELECT * dbo.sysjobs). Whenever you are querying SQL Server System Tables and you need more info, MS Docs is always the place to turn to, as linked above in this post.
Logs are always important if you’re supporting systems. The SQL Server Agent has always been a heavily used tool in my experience, so writing execution trace messages to the logs might help investigate an issue one day.
Because this option can cause the error log to become large, only include execution trace messages in SQL Server Agent error logs when investigating a specific SQL Server Agent problem.
I’ve never enabled this before and discovered the option today. It does appear to make a big difference in the volume of Error Logs.
Write Execution Trace Messages to the SQL Agent Error Log
1. Right-click SQL Server Agent within the SSMS Object Explorer.
2. Enable the ‘Include execution trace messages’ box.
Running scheduled tasks in SQL Server Express has to be done differently due to the limitations of the Edition. As well as having a maximum database size of 10GB and 1GB consumable RAM, Express does include the SQL Server Agent. So, If you need a task to run on a time-based schedule, you’ll have to look outside of SQL Server.
We can schedule our jobs using the Windows Task Scheduler, which will run a PowerShell or DOS script that calls the sqlcmd.
The .bat script for this post will execute Ola Hallengren’s Index Maintenance, which I’ll set to run weekly on the MS SQL Express instance. Ola’s index maintenance stored procedure is assumed to be on the SQL Server for this task.
If you already have SSMS installed, you can find the directory of sqlcmd with the help of this Microsoft Documentation Page – File Locations for Default and Named Instances of SQL Server (search ‘sqlcmd’ after selecting your SQL Version when on the page).
My sqlcmd.exe and bcp.exe clients are in: -> C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Bin
The only thing we need for now is to have it installed on the same machine we’re running the script from.
I wrote another blog post on sqlcmd if you would like more information and examples of executing SQL commands with sqlcmd.exe.
Below you’ll see -Q being used and everything within
the command is within quotes. The -o parameter is also being used to
forward output messages to a text file.
We have our script file ready, and we know where SQLCMD is. It is time for us to schedule the script as a Task in Windows Task Scheduler. This should be a more familiar area for Windows Admins.
1. Open Task Scheduler.
2. Right-click and select Create New Task as shown below.
3. Within the General tab of the prompted window, change the following: –> Name / Description, more information the better for visibility. –> The user account that the task runs as to a new local service account I created for this. Remember, the chosen account requires Logon As Batch and MSSQL permissions (sysadmin for quickness here). –> Run whether the user is logged on or not.
4. Click the Triggers tab and hit New to set the schedule.
The schedule below is set to run every Sunday at 02:00. Set this up to your desired days/times and click OK once done.
5. Click on the Actions tab and click New to add our script as the action.
This is where we add our .bat script file location. Browse to the folder you saved your script and click OK when after selecting the file.
6. Click on the Settings tab and review options. The “Stop task if it runs for longer than” setting is of particular note as the default is 3 days.
7. Now that we have reviewed all tabs within the Create Task window, click OK to create the task.
You will be prompted to enter a username and password if the task is set to use an AD service account.
This Scheduled Task has been created and is awaiting its next execution, which is early Sunday morning.
Your script might not be running for long enough to verify this way. This maintenance solution script run time can vary as it depends on the level of index fragmentation on all SQL Server databases. It’s been run 64 times on my test databases and now only taking a second to complete.
We can also verify the SQLCMD script ran successfully by checking the Scheduled Task History and log output.
If you try to change the order of steps in an Agent Job within SQL Server Management Studio (SSMS), you may encounter the error “Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index (mscorlib)“.
This error can occur even if you are using the latest version of SSMS. It’s unclear what the root cause of this issue is, but I have been able to resolve it for myself and some others by updating to the latest version of SSMS.
Resolve “Index was out of range” SSMS Issue
As mentioned above the resolution is to update to the latest SSMS version. For demo purposes, I’m going to show the actual error I was encountering here.
I create a new SQL Agent Job with 3 job steps:
When I try move the job step 3 (import_second_table) run order from 3 to 2, the Index was out of range error prompts:
Searching for this error online often led me to a lot of unrelated information about .NET development. If updating SQL Server Management Studio (SSMS) has not resolved this issue for you, feel free to leave a comment and we can troubleshoot here.
