SQL Server may have many jobs and if you have a requirement of scripting them, if you are using object explorer you cannot scripting them all at once as shown in the below screen.
Go to View -> select Object Explorer Details, you will be taken to following screen.
From then above list either you can select all the jobs or jobs you need to script. Then you can create script as shown in the below screen.
If you are running SQL Server jobs with numerous steps there can be failures due to network glitches or deadlocks. In such a scenario the DBA will typically restart the job from the failed step. However, there is an option in SQL Server Agent to automatically retry the job, which is under utilized by many DBAs. This faq will tell you how to do it.
I came across new two new reports in SSMS. (This report is available in SQL Server 2008 R2 and onwards). Those are coming under SQL Server Agent as shown below.
This report gave me this.
You will see in the key, there are two of # Successful Executions in green and in purple color!. I have never seen a chart key like this. Have you?
Difference between the purple and green is, Green “# Successful execution” for the successfully completed jobs while the purple one is for the canceled jobs. Should it be nice, if we have a different text for this, rather than having the same name for both.
When I posted an faq about Finding Running SQL Server Agent Jobs I had few emails and queries on how verify whether a job is running by using only a TSQL script. This FAQ will verify if the job exists and whether or not it is running.
Monitoring SQL Server agent jobs is a critical task for DBAs. There are several ways of achieving this. Read this FAQ to find them.
