March 7, 2017 at 11:50 am
I have stored procedure which has cursor.That procedure executes fine during all times except Monday morning.I noticed close cursor command missing in the proc.Procedure has a parameter execution time which is passed.How to debug this procedure, the procedure which has issue is sub procedure of a main procedure.
March 7, 2017 at 12:08 pm
When you say "executes find during all times except Monday morning.", what actually happens on Monday morning? Does it fail? Does it just not finish? How is it being executed? If it fails, are there error logs to look at? You've also not provided any code for any of the procedures, so I don't know how you could expect anyone to help much.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 7, 2017 at 12:10 pm
savibp3 - Tuesday, March 7, 2017 11:50 AMI have stored procedure which has cursor.That procedure executes fine during all times except Monday morning.I noticed close cursor command missing in the proc.Procedure has a parameter execution time which is passed.How to debug this procedure, the procedure which has issue is sub procedure of a main procedure.
You would need to give more information.
My suggestion is that you document everything that is done on this procedure and then replicate it in a set based way to remove the cursor (loop).
I know the suggestion is vague, but so is the question.
March 7, 2017 at 12:15 pm
sgmunson - Tuesday, March 7, 2017 12:08 PMWhen you say "executes find during all times except Monday morning.", what actually happens on Monday morning? Does it fail? Does it just not finish? How is it being executed? If it fails, are there error logs to look at? You've also not provided any code for any of the procedures, so I don't know how you could expect anyone to help much.
Cursor hangs on monday morning it is executed in ASEMON sp_sample_proc @retryattempts =75.its hangs on between 4: 30 and 5: 30 am.
March 7, 2017 at 12:22 pm
Okay, so it hangs... is there a wait condition or blocking going on, perhaps caused by index maintenance, backups, or ETL activity? Check with your DBA...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 8, 2017 at 3:43 am
sgmunson - Tuesday, March 7, 2017 12:22 PMOkay, so it hangs... is there a wait condition or blocking going on, perhaps caused by index maintenance, backups, or ETL activity? Check with your DBA...
Cursor doesn't hang but runs in constant loop for some reason ,maybe due to Halloween effect.
March 8, 2017 at 7:09 am
Well, cursors are loops... if not written correctly, they can go into an infinite loop, but as you say it only occurs on Monday mornings, it seems more likely that some form of wait condition has been encountered. Chances are, something else going on in the system is locking a resource needed by that cursor, and you're not going to fix it by not taking my previous advice. CHECK WITH YOUR DBA to see what is going on at that time on Monday mornings. And as mentioned multiple times by other folks responding to your initial post, see if you can find a way to get rid of the cursor, and instead use a set-based methodology. Oh, one more thing. Don't double post. Posting a second time isn't going to help... most folks will see it, recognize it, and be a lot less willing to assist.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply