How To Debug a job that failed in SQL Server 2016 ( This process of troubleshooting failed jobs has been the same at least since SS2005)
- Make sure SQL Agent is running on the server in question.
- Go to the job and right click. Select “View History”
- The Log File Viewer window will show. On that window select the latest failed job time, click the + symbol to open up the steps, and then click on the failed step. View the error in the lower grey area. (I didn’t pass coloring in the lines in Kinder)
- Go to the job step properties and find out what procedure, query, SSIS, etc. was running at the time of the failure.
- right click on the job name and select properties
- In the Job Properties Window, select steps.
- In the job step list, select the step that failed.
- click on the Edit button on the bottom of the window
- The Job Step Properties window will open. Note the Type of task, the database, and the command.
From here troubleshooting will depend on the type of task. For my issue, the task was a T-SQL procedure. So I will walk through what to do if the step is a procedure.
- If there is a development environment, go to that environment for troubleshooting.
- Find the database, click the plus symbol next to the database to expand the directory.
- Click the plus symbol next to Programmability
- Click on the plus symbol next to Stored Procedures
- select the procedure and right click, then select Modify
- From here I take each select statement and run individually to see if the error is in the selects.
- Then I make a copy of the table that will be inserted into or updated, or deleted from and change the procedure code to point to the test table. Then I run the procedure to see if the error is in the update, insert, or delete.
- Usually you will find the error and be able to fix from here.