SQL Server monitors job execution

Recently, some jobs in the database cannot be executed very healthily. However, it is very troublesome to log on to the DB server to view error logs during monitoring, and jobs are generally important, for example, back up a database or clear a database. On the website side, it is often because the cleanup job cannot be normally executed, resulting in a database space increase of 2G-3G in a few days. Finally, the troubleshooting found that, it is because the execution of the cleaned SQL statement times out. Therefore, a job execution report function is provided, which is integrated into the company's background and put together with error logs to facilitate daily monitoring.

The following are the SQL statements used:

 
Select top 150. run_date,. run_time, B. name, step_id, step_name,. message,. run_status,. run_duration from MSDB. DBO. sysjobhistory A, MSDB. DBO. sysjobs B where. job_id = B. job_id and name not in ('job _ exclude ') and. step_id> 0 order by run_date DESC
 

This function mainly uses two job system tables: MSDB. DBO. sysjobhistory and MSDB. DBO. sysjobs.

Job_exclude: it is a job that runs very frequently and can be excluded, for example, once every 5 minutes. Monitoring does not make much sense.

The following is a function diagram:

In fact, there are more and better ways to maintain the database. Later, we will consider using web pages to monitor database health information, such as disk space usage, this can be closely linked with the internal background for easy monitoring

I hope it will help you. Please click here for more suggestions. Thank you.

RelatedArticle:

SQL Server System Table analysis (1)-Backup table

SQL Server monitors job execution

Find yourself-2011/04/18-. net, C #, ASP. NET-starter blog