delete a stored procedure while executing

Question

Hi 

is it possible to delete a stored procedure while it is in execution , for example if svr1 is executing the stored procedure "abc" if it possible to svr2 to go and delete "abc" in the same DB.

thanks.

Answer 1

Interesting question - if you have two servers, can you try this scenario? I would assume that you can delete the SP during its execution, but it will be nice if you can test it and let us know.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Hello,

To drop an existing stored procedure an exclusive lock on the SP is required and as long someone accesses the SP / execute it, there is already a shared lock on the SP, so it's not possible to get an XLock and therefore you can't delete the SP as long as it's running.

---

Olaf Helper
Blog Xing

Answer 3

Hi Naomi,

Please correct me if i am wrong. The scenario as mentioned above can be possible only if their is no transaction being started  but if the Svr1 is in transaction within which Abc is being called and again the transaction is closed based on Abc out put then Abc cannot be deleted right.

---

Please have look on the comment

Answer 4

Thanks for your support , so in simple since svr1 is having a shared lock on "abc" svr2 cannot get an exclusive lock on "abc" , so it cannot delete the stored procedure.

Answer 5

Exactly due to shared lock ist unable to delete until procedure gets excute completely

---

Please have look on the comment

Answer 6

Hi Olaf/Naomi,

I dont understand fully.... Please help me.As below I created an SP and I could drop the proc successfully. Please test and correct me if am wrong.

create proc AAAA
As
 Begin
	Select 1
	WaitFor delay '1:00'
	Select 2
 End

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 7

Are you able to drop this procedure while it's executing? E.g. in one session run execute dbo.AAAA and in another query window try

drop procedure dbo.AAAA

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 8

i think it is possible.. test it..

create table vttest(i int)

create proc ct as
begin
	declare @i int=1
	while @i<1000000
	begin

		set @i+=1
		insert into vttest values(@i)
	end
	select 1
end

open two query window

window 1

begin  tran
exec ct


rolllback -- execute only after finishing the ct

window 2

drop procedure ct

While ct is executing in window 1 move to window 2 and drop it

after ct is finished .. run the the rollback

VT

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 9

Yes. Please do a test.

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 10

Yes, I've been able to drop the procedure while it was executing.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 11

yes , I also tried and was able to delete it , seems to be this is some other scenario than Olaf Helper explained .

Answer 12

Experts correct me if I am wrong...

It is cmd parsers responsibility to check the all the objects used in batch exists  in the database or not before passing it to optimizer and then to query executor.

One the batch has reached query executor it doesn't communicate back to optimizer  or cmd parsers, it communicate only to the protocol layer.

So it is possible to delete an SP by other user while someone else is executing it..

I am sure we have got lots of SQL Wizards in this forum  who know sql server in and out can provide much accurate answer....

vt

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 13

I'm able to drop the sp during execution.

Even I tried using different accounts.

I execute the sp by user1, and then I tried to drop by another user (user2).

it was drop that sp.

regards

amchtwe

Answer 14

I just tried few things and giving the observation as below as its looking interesting to me. Please correct me if any of the below is wrong...

Olaf's answer is right that there is a shared lock is acquiring,however not on SP but on PLANGUIDE. There, it does not look acquiring a lock on SP object but on PLANGUIDE.However, the lock acquired is only for fraction of seconds and its getting released.

In our testing, we are executing the proc in one window and in another window, we are dropping it. I am assuming the Shared lock acquired on PLANGUIDE will be released by the time when we are executing the DROP. Once the shared lock is released, it does not look any other lock aquiring on SP.Hence, it allows us to drop it successfully.

Profiling results are below:

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!