View the History of Jobs with SQL Jobs History Feature

Introduction

Admin people on a day-to-day basis schedule a lot of SQL jobs and must monitor its success and failure statuses constantly. In Biztalk360 we have already provided the capability for the users to do some operations and monitoring on SQL servers. Here comes a new feature called SQL Jobs History to view the history of the jobs that have been scheduled on the server agent and this article holds more details about the new feature.

Why SQL Jobs History in BizTalk360

• SQL Jobs History will give a clear picture on an overall level about the status of the scheduled job.
• Biztalk360 will become a one-stop solution for admin people to handle operations and monitoring concerning SQL server agents.
• Helps the user by providing a view of the job history and reduces the navigation between different applications.
• The created jobs get displayed in BizTalk360 and the jobs history can be viewed by clicking the eye icon both in SQL server jobs and in SQL jobs history.

Jobs in SSMS (SQL Server Management Studio)

Jobs that are created in SQL Server Management Studio and its history will be displayed in Biztalk360. The below section will highlight how to create jobs in SSMS (SQL Server Management Studio).

1. Click on the SQL server agent select new and create a job.

2. Add Job Name, Category, and Description and click the OK button.

3. To monitor the jobs first we must start the SQL server agent.

4. Renaming the jobs can be done in SSMS (SQL Server Management Studio).

5. We can also view the execution history of the job by right-click and selecting View History from the context menu.

6. Green colour shows the job executed successfully and red colour shows the job gets failed.

Key components of SQL Server Agent

SQL agent service consists of the following components and defines a task to be performed. The below-mentioned components help to perform the tasks and describe the success and failure of the tasks.

There are a few components of the SQL Server Agent service.

1. General – This defines the name of the job; owner of the SQL server and we can categorize the job and its description too.
2. Steps– Job can contain one or multiple steps. Each step executes a specific set of instructions and contains its own task. The next step will be executed based on the status of the previous step.
3. Schedules– Jobs can be scheduled hourly, daily, weekly, monthly and the schedule type can also be set.
4. Alerts– Alerts triggered based on the job’s execution.
5. Notifications–User can set up email notifications to get updates about the result of the job execution. It throws notification when the jobs fail so that appropriate action can be taken.

SQL Jobs History

SQL Jobs History tab in BizTalk360 will help the user to view the history of jobs on a single page rather than going to SSMS (SQL Server Management Studio) for the same details. SQL Jobs History tab has the recent records of jobs run and listed according to the max records selected. SQL Jobs history can be accessed in BizTalk360 via Operations->Manage Infrastructure->SQL Server Instances->SQL Jobs History. The detailed history can be viewed by clicking the eye icon in each row.

In the previous version of the BizTalk360, only the jobs were getting displayed under SQL server jobs. For example, let us take a scenario. If the user needs to view the history of the job, the user must navigate to the SSMS (SQL Server Management Studio) to view the history. So, to overcome such a process, we have implemented SQL Jobs History and it becomes more helpful for the user to audit all the history of the jobs instead of viewing in SSMS (SQL Server Management Studio). 

SQL Jobs History details blade

The importance to add general details is that a user can be able to view the primary details of the job. BizTalk360 provides a user-friendly structure when compared to log file viewer in SSMS (SQL Server Management Studio). 

General tab:

1. Job Name
2. Owner Name
3. Run Status
4. Category
5. Created Date Time
6. Modified Date Time
7. Last Modified Date Time
8. Description

In the step tab, only the step execution history gets displayed. All the step details can be viewed in a single tab. 

Step tab:

1. Step Id
2. Step Name
3. Run Status
4. Last Executed Date Time
5. Description

SQL Server Jobs enhancements

All the available jobs get listed under SQL server jobs. For better usability grid crawler has been implemented. When we hit the eye icon, we can view the details of the job by clicking the Next and Previous arrow continuously to view the jobs detail instead of viewing all the jobs history separately. Succeeded, Failed, Cancelled, and unknown status of the jobs gets displayed.

Parent and child job details can be viewed in the SQL jobs history blade on expanding the parent job.

SQL Jobs History blade holds the Steps of the Job ran. For example (step 1, step2.).

Succeeded Jobs are shown in green colour and Failed Jobs are shown in red colour code in SQL Jobs History blade.

On clicking the eye icon in the child grid, we can view the below step details in the SQL Jobs History Details blade:

1. Step Id
2. Step Name
3. Run Status
4. Last Executed Date Time
5. Description

Filters in SQL Jobs History

SQL jobs history comes with different filters to filter their specific data. There are more jobs run at the same time. If the user needs to audit a particular job, it is hard to search, and it is time-consuming. To handle such challenges, we can use the filter by option in SQL Jobs History.

Filter by option comes with below set of filters

• Job Name:
  • Job name filter list all the jobs name in SQL server jobs.
• Run Status:
  • Run status filter list all the status:
    • Succeeded
    • Failed
    • Cancelled
    • Unknown
• Run Date Time:
  • Run Date Time filter holds the date time picker in which user can modified the date and time accordingly.
• Max Records:
  • Max records fetches the list of values according to user selection (i.e., 10/25/50/100) and user can also customize their own values in setting section.

Conclusion

In earlier versions, there was no option to view the history of jobs which makes it difficult for users who are looking for history details as they must go SSMS (SQL Server Management Studio) every time to view those details. By adding the SQL Jobs History feature the robustness of BizTalk360 has increased and helps the user with all the jobs-related data in a single place. We believe this new feature will add value to your day-to-day tasks by reducing your redundant efforts for those who need to view jobs history. We have a free trial for you, give a try!!!