How to Troubleshoot a failed Job

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)

  1. Make sure SQL Agent is running on the server in question.
  2. Go to the job and right click. Select “View History”viewHistory
  3. 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)viewHistorylist
  4. Go to the job step properties and find out what procedure, query, SSIS, etc. was running at the time of the failure.
    1. right click on the job name and select properties
    2. In the Job Properties Window, select steps.
    3. In the job step list, select the step that failed.
    4. click on the Edit button on the bottom of the windowJobPropertiesWindow.JPG
    5. The Job Step Properties window will open. Note the Type of task, the database, and the command. JobStepProp

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.

  1. If there is a development environment, go to that environment for troubleshooting.
  2. Find the database, click the plus symbol next to the database to expand the directory.
  3. Click the plus symbol next to Programmability
  4. Click on the plus symbol next to Stored Procedures
  5. select the procedure and right click, then select ModifyModifyProc.JPG
  6. From here I take each select statement and run individually to see if the error is in the selects.
  7. 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.
  8. Usually you will find the error and be able to fix from here.

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s