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_autonotification in it, it hangs.
SQL Server Agent is running under a domain service account. I have almost 100 other jobs that are running fine.
Thank you in advance.