December 7, 2012 at 9:32 am
Hello
i need help.
I have one SQL job,its basically one store procedure to call within the JOB. It usually take upto 1 hours to finish. but right now it stuck one some point and running for almost 2 days.
one time i stop and kill that spid and restart the job manually but it still running.
and the SPID that i killed, its in KILLED/ROLLBACK status when i check user Activity on server.
do i need to restart Database server to restart all this running job and process?
Please help me to resolve this issue.
December 7, 2012 at 9:41 am
Does the SP do anything outside sql server?
Send emails, run dos commands, access other servers, create a new spid...
These could all be blocked and the cause the kill to be blocked waiting for an external process to complete.
Do you have any logging within the SP? If you log each statement then you can see which is causing the issue.
Look at the command that the spid is hanging on.
Not surprised that if one copy of the sp hasn't completed that another doesn't either.
Cursors never.
DTS - only when needed and never to control.
December 7, 2012 at 10:22 am
nigelrivett (12/7/2012)
Does the SP do anything outside sql server?Send emails, run dos commands, access other servers, create a new spid...
Yes, basically this Store Proc call to another procedure that import updates to the table
Do you have any logging within the SP? If you log each statement then you can see which is causing the issue.
You mean to see the log error for SP. I don't know how to check.
can you please direct me to do that
Look at the command that the spid is hanging on.
The Command it some me a sql sever Job
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply