Brad’s Sure Guide to SQL Server Maintenance Plans- P28: SQL Server has a reputation as being a simple database application to install, configure, and maintain. This is a little misleading. SQL Server is a powerful relational database that can handle the needs of the largest organizations and, as such, its proper maintenance almost certainly requires the attention of an experienced DBA. | Chapter 10 Execute SQL Server Agent Job Task For example you may have a custom job that checks disk space and sends you a warning if it s getting near full kicks off a SQL Trace script to capture trace data on a scheduled basis checks a particular value or values in a DMV that you are interested in monitoring and sends you an alert if the value s exceeds a predefined threshold starts a job that copies local MDF and LDF backups from off the local SQL Server instance to another server location preferably offsite . As discussed in the introduction if you find yourself wanting to add multiple SQL Server Agent jobs to the plans you re creating using the Wizard you re probably better off avoiding the Wizard in the first place and using T-SQL or PowerShell scripting. When the Execute SQL Server Agent Job task runs T-SQL similar to the following is executed. EXEC @job_id N cb73ea96-9a96-49fe-ada9-a70a941f9fb9 Notice that this is the execution of a system-stored procedure which is instructed to run a job with a specified internal number. The number is not very useful to us but if you want to look it up in order to find out exactly what job was run assuming you don t know you could run the following SELECT statement SELECT FROM This query will display all job IDs along with their job names so you can easily identify which job ID matches which job name. When an Execute SQL Server Agent Job task runs it produces a text report similar to the following Microsoft R Server Maintenance Utility Unicode Version Report was generated on HAWAII. Maintenance Plan MaintenancePlan Duration 00 00 00 Status Succeeded. Details Execute SQL Server Agent Job HAWAII Execute Job on Local server connection Job name Send Alert If Disk Space Exceeds 80 of Capacity Task start 2009-07-30T16 06 17. Task end 2009-07-30T16 06 17. 136 Chapter 10 Execute SQL Server Agent Job Task Success Command EXEC @job_id N .
