SQL Server Job History is missing

If you are 100% sure that the job configured with SQL Server Agent has been executed successfully but you do not see the execution record when the Job History is opened, what could be the reason?

This is something I noticed yesterday with one of companies I consult. The client has many jobs configured with SQL Server Agent and all work fine without any issue. When the job history of the job is viewed using View History context menu, for some jobs, it shows the history but not for all. If the jobs have been successfully executed, it means that execution records should be exist in the msdb database as it is the database that stores all these information. So, we immediately checked the database;

USE msdb;
GO

select * from sysjobhistory;

It returned only 1000 records, means it maintains only thousand records. This is based on a setting of SQL Server Agent which is called Current job history log size (in rows). When it was checked, the setting was something like below;

This setting deletes all old records to make sure that it maintains only 1000 records. That is the reason we see the history for some records, not for all. What we saw from the above query is, details of jobs executed recently.

Best is, select Remove agent history checkbox and set to 4 weeks or more without using Limit size of job history log. This makes sure that we see the history for last one month. However, if the msdb database is getting larger, then you need to configure a lower value.

SQL Server Agent Error are logged in SQL Server Error Log or somewhere else?

I have written two posts on SQL Server Error Log; How to delete current SQL Server error log? My current log is in GBs and Reading SQL Server Error Log using XP_READERRORLOG, and they explain how the log is maintained, how log files are created and how the content can be read. I was asked a question on this but not exactly on SQL Server errors but SQL Server Agent errors.

Can we read/see SQL Server Agent errors in SQL Server Error Logs? Or should we see somewhere else for this? Generally, you should see all errors related to SQL Server but errors related to Agent are maintained somewhere else called Agent Error Log.

Just like the way SQL Server maintains its error logs, multiple files are maintained for Agent error logs. By default there are 10 files and they are saved in Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log folder.

The file named as SQLAGENT.OUT is the current one and just like the SQL Server Error logs, this gets recycled when the service is restarted. Once recycled, current one becomes SQLAGENT.1 and old SQLAGENT.1 becomes SQLAGENT.2, and so on. However, using Congfigure menu option in SQL Server Error Log node, we can adjust the number of files to be maintained but that facility is not exist with Agent Error Log.

If you see that the current log file is larger than you expect, it can be recycled by either by clicking the Recylce menu item in the context menu (see above image) or executing dbo.sp_cycle_agent_errorlog stored procedure.

How to create a SQL Server job quickly

SQL Server Agent Jobs are commonly used for automating administrative tasks and other routine tasks. For creating a job as you need, you need to either use GUI that comes with SSMS or use stored procedures given. Yes, it takes time for adding a job and then adding steps but we can get some jobs automatically created using a simple way.

Assume that you need to create an Agent job for backing up one of your databases. Rather going through SQL Server Agent -> Job in the Object Explorer, you can open the Backup Interface just like the way you manually take backup, configure everything as you want. Once the configuration is done, without clicking OK, click on down-arrow next to Script Button and click on Script Action to Job.

This action opens a window that is used for creating Agent Jobs. Good thing is, it adds all we need with Steps and all you have to add is a schedule.

Using this technique, jobs can be easily created for most of the tasks.

Troubleshooting agent jobs

Administrators always try to automate routine tasks, making sure that all required operations are done on time without failures. The main component used for automation is SQL Server Agent, which is used for creating jobs. If you have noticed that a scheduled job has not run or all jobs are not running, how do you troubleshoot? How do you start troubleshooting?

Here are some guidelines for troubleshooting based on a discussion I had;

• First thing needs to be checked is whether SQL Server Agent service is running or not. You need to make sure that Startup type is set to automatic, this makes sure that service is started when the server is restarted. If, for some reasons, service is not running, and you cannot even manually start it, check following;
• Check and see whether account assigned for the Agent is valid and no issues with the password. The account assigned may have been expired, changed or disabled. Check the system log for more details.
• Check the msdb database. If it is corrupted or offline, Agent will not be started.
• Check the job history. Check whether last run was successful. There can be some issues with business logic implemented.
• Check whether the job is enabled. Someone might have disabled it.
• Check whether the schedule set is either expired or disabled.
• Check and see whether proxy accounts are working properly if you have used. Check credentials used for proxy accounts.
• Check dependencies. There can be steps in the job that run without any issues but some. Check whether all required items, such as files, folders and all required permissions for accessing are available.

Can we get SQL Server service and Agent service automatically restarted at unexpected stopped?

We know that Windows Services have an option to get them started automatically configuring the Start Mode. But this is only with Windows starting. What if when either SQL Server or SQL Server Agent stops unexpectedly, you need to get them started automatically? 

This is possible. It can be simply set with SQL Server Agent property window as below.

** Note that, for the second option which is Auto restart SQL Server Agent if it stops unexpectedly needs SQL Server Agent Service Account to be a member of local admin group of installed server. However this is not considered as a good practice, hence external monitoring tool such as System Center Operations Manager is recommended for monitoring.