I currently have an important TSQL job that hangs probably once a week.
It moves data from one database to another.
It is on a schedule running every 5 minutes indefinitely throughout the day.
Sporadically, it will hang usually around 1 am in the morning. When I say hang I mean it just gets caught at 1am never finishes its current scheduled run in turn it never moves on to the next scheduled run.
In order to get it to stop I have have to stop the job. Disable the job. Kill the associated SPID. Re-enable and restart the job. Sometimes this gets it running but other times I have to restart the SQL Agent itself.
There is really no rhythm or reason as to why this happens datawise. Sometimes it will go weeks without issue other times it occurs once or twice a week.
I am just wondering if there is possibly a way to have this job restart it self regularly in the morning so it doesn't hang. I am assuming someone else has had an issue like this before. Any direction here is greatly appreciated.
There is no blocking in the database. I am not looking to fix a blocking issue. Looking for a way to determine if the job is hung and restart it or something along those lines.
I have reviewed
sp_who2 regularly and never seen any blocking with this particular job.