question

JCACERES avatar image
JCACERES asked

How to avoid delays in Sql Server Agent Jobs every certain time?

Hi, I have a SQL agent with 2 steps of SSIS packages and running every 10 seconds, every certain time the agent is taking longer than unusual, I don't if it is the database that is not allowing query constantly or an issue with the job. I want to be able to get an average of 7-8 minutes which is acceptable. Here is an image with the issue highlighted: ![Image][1] [1]: /storage/temp/4562-issue.png
ssissql server 2014database-mirroringagentdatawarehouse
issue.png (32.5 KiB)
issue.png (32.5 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Ange avatar image
Ange answered
A few things to check here. Are the times of the long running queries consistent? For example does it generally happen around 5:27AM. Are there any other jobs running at these times. What kind of workload is on the database/server at those times. Is it across multiple servers? could there be a network lag at that point in time. Lots to conside and it very much depends on your system. I have multiple jobs running and the timing can vary like yours simply because other things are happening on the server/database at that time. If other things are happening it could be competing for resources. Could be anything from an insert causing a lock into a table that your job is trying to query, to updates on the server.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.