dteshome
asked on
SQL Server Agent JOB running Indefinitely!
I have a SQL Server (2005) job that executes a stored procedure.
The job used to run fine but lately, it hangs for hours (typically finishes in 2 -3 hrs).
So I end up killing it.
When I run the SP from SSMS, it runs and completes. I have created a brand new job, owner, sa, and have only a single command: exec usp_generate_autonotificat
SQL Server Agent is running under a domain service account. I have almost 100 other jobs that are running fine.
Thank you in advance.
D
The job used to run fine but lately, it hangs for hours (typically finishes in 2 -3 hrs).
So I end up killing it.
When I run the SP from SSMS, it runs and completes. I have created a brand new job, owner, sa, and have only a single command: exec usp_generate_autonotificat
SQL Server Agent is running under a domain service account. I have almost 100 other jobs that are running fine.
Thank you in advance.
D
While the job is executing, check in the DMVs on what steps of the procedure are in execution.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Are you creating new log file every time or appending the content to the existing log file? Can you explain what are you logging over 25MB?
ASKER
I am actually appending, however, the job processes several hundred thou records of persons who ask to be notified ... and each run of the job (particularly on the weekends when realestate activity is high) may generate a log that is 10, 20, 30 MB of log ... what is being logged is not generated by the job per se, I believe the SPs that are called generate what goes into the log.
ASKER
I figured out what was going on ...
The sol'ns provided were not helpful in this case.
I am actually appending, however, the job processes several hundred thou records of persons who ask to be notified ... and each run of the job (particularly on the weekends when realestate activity is high) may generate a log that is 10, 20, 30 MB of log ... what is being logged is not generated by the job per se, I believe the SPs that are called generate what goes into the log.
The sol'ns provided were not helpful in this case.
I am actually appending, however, the job processes several hundred thou records of persons who ask to be notified ... and each run of the job (particularly on the weekends when realestate activity is high) may generate a log that is 10, 20, 30 MB of log ... what is being logged is not generated by the job per se, I believe the SPs that are called generate what goes into the log.
you should trace the job using profiler and see whats happening.