Troubleshooting the processing job if there are errors

book

Article ID: 184486

calendar_today

Updated On:

Products

Information Centric Analytics Data Loss Prevention Core Package

Issue/Introduction

Data not updated in ICA, data missing in ICA.

In the SQL Agent History dialog box, a yellow triangle with an exclamation mark will be displayed.

By highlighting the row, error messages may be displayed.

Cause

Multiple. Below are the most common:
  • Source Server is offline/unavailable
  • Network and/or connectivity issues
  • Source DB is offline
  • Access/Security Permissions
  • IW Mapping
  • Risk Fabric Stored Procedure

Resolution

Source Server is offline or unavailable. This may be due to a number of reasons:

  • Patch, upgrade, or maintenance windows. These are usually scheduled and can be added to a calendar for tracking so the team can be made aware in advance. Disabling the job during that window is an option as well. Confirm that schedule with the customer and the maintenance team. “Server Unavailable” or if a query was run with the error “deferred prepare could not be complete” are good indicators.
  • Disk Space – Most commonly seen when the tempDB fills up when running through calculations. Purging the tempDB will help, but long term solution would be to increase the size of the tempDB or the drive it resides on.

Network and Connectivity Issues:

  • The customer’s network may have had an issue, maintenance, or upgrades that impacted the jobs ability to connect to a remote server. This could also imply changes to firewall settings, or other network specific changes. 
  • First, right click on the linked server and click “Test Connection.” If it works, then it may have been isolated to the time the processing job ran. Talk with your customer contacts and the customer network team to review any potential changes.  “Server Unavailable” or if a query was run with the error “deferred prepare could not be complete” are good indicators.

Source DB is offline:

  • This could happen due to an application upgrade window, the server was rebooted, taken down, updated during the processing window, or the DB name was changed. Be sure to work with the customer to identify these maintenance windows and changes. 
  • “Named Pipes Provider, Error 40” is a good indicator error. Be sure to test the linked server by right-clicking on the server and clicking on “Test Connection” to verify connectivity. If the database was renamed, escalate to Services and Engineering to update the Linked Server properly.

Access/Security Permissions:

  • The password or permissions may get changed on the account being used to connect to the source database. This is most commonly seen in development/lab environments as production and QA environments have more stable service accounts established. 
  • Errors in the step like “Could not connect to ‘linkedservername.’ Provided credentials…” or something similar.

IW Mapping: 

  • While infrequent, this may be more related to the database name being changed or a table name being changed. This is most common when dealing with custom data sources.  
  • Check the [Log_Transformation] table for any rows, for the current day’s run, that have a StatusFlag of “F.”

Risk Fabric Stored Procedure:

  • If all the items above have checked out with no issue and there are errors in the SQL Agent History log or in the [Log_Transformation] table, and you are still seeing errors, this may be related to an issue with the out of the box Stored Procedure.
  • First, check the history, or past emails, to see if the step ran successfully previously. If yes, then this may be more an environmental issue listed above.
  • If sporadic, it may also be related to an environmental issue that must be investigated.
  • If the step has never run successfully and all the items check out OK above, there may be an issue with the associated Stored Procedure
  • First, identify the Job Agent Step that is throwing the error.  If there is no error listed, open up the Risk Fabric database and run this query against the Log_Transformation table. Be sure to replace the value <Today’s Date> with the current day’s date in the following format: 2016-01-01 SELECT * from LogTransformation WHERE (StartDate > ‘<Today’s Date>’) AND StatusFlag = ‘F’
  • The query above will give you all the associated stored procedures that failed in the current day’s processing.
  • Document the name of the Stored Procedure and the error associated.
  • Most commonly associated to a column name not being specified or is the incorrect type.
  • Escalate these issues to Support for further troubleshooting and documentation.

Attachments