There are two processes(p1, p2) that may run simultaneously. p2 is a scheduled execution of SPROC while p1 consists of a group of stored procedures that is triggered on the request. Execution of p1 while p2 is in progress can create issues. Only one should run at a time. There are three ways to solve this problem
- When p1 starts check whether p2 is in progress and wait until it completes. p2 can run for more than a day and it might not be a preferable solution
- Kill p2, complete p1 and then restart p2. Killing and restarting p2 isn’t safe due to nature of the sproc
- pause p2 and resume p2 when p1 completes
Questions
1. How can I search and stop store procedure being executed?
2. Is there any way I can pause and resume a stored procedure that is being executed?
