SQL Agent 2005 Reporting Success on Timeout

• keithwarnock

  Valued Member

  Points: 50

  More actions

  March 10, 2008 at 8:40 am

  #183883

  I have a job that completes correctly sometimes and occasionally has a timeout at exactly 10:00 minutes and the log reports that the job completes successfully. Here is the message I received:

  Date3/8/2008 3:28:23 PM

  Log

  Step ID9

  Serverqqqq

  Job Nameqqqq

  Step Nameqqqq

  Duration00:10:00

  Sql Severity0

  Sql Message ID7412

  Operator Emailed

  Operator Net sent

  Operator Paged

  Retries Attempted0

  Message

  Executed as user: ‘userid’. OLE DB provider "SQLNCLI" for linked server "linked_server_name" returned message "Query timeout expired". [SQLSTATE 01000] (Message 7412). The step succeeded.

  I have set the connection timeout on the destination server for remote connections to ‘0’ (no timeout). The step that has the timeout is the last in a sequence of 9 steps. The entire job usually completes in and hour and a half.

  I have setup a notification to fire when this error message (7412) occurs but, I would like to either re-run the job or have the step fail.

  Any suggestions to avoid this incorrect reporting or resolve this issue?

  Thanks!

• Big Data Man

  SSCrazy Eights

  Points: 8536

  More actions

  March 10, 2008 at 8:52 am

  #787412

  Hi keith,

  Have you checked remote server for possible cause of timeout? I would review remote server to understand what happens around this time, as it seems consistent.

  Thanks,

  Phillip Cox

  MCITP - DBAdmin

• keithwarnock

  Valued Member

  Points: 50

  More actions

  March 10, 2008 at 11:21 am

  #787512

  Phil,

  We do have some activity on the destination DB during that time however, we are unable to isolate or stop users from accessing the DB during that time.

  Would there be anything from a system or programming level that we would be able to control this with?

  Thanks

  KW