April 10, 2003 at 9:32 am
Hi,
Per my developer request, I am trying to drop two procedures (since they are getting the same error, though they have dbo privilages). And its diaplying the following error message:
<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>"Cannot drop the procedure 'dbo.p2_view_filter_share1' because it is currently in use"<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
I have checked with Database monitor tool of DBArtisan, and could not find anything with this name being used.
Any thoughts????
.
April 10, 2003 at 10:08 am
Can you run an sp_who2 and see if there are any connections that are not "sleeping". If so, run a dbcc inputbuffer with the spid value and be sure someone is not running a procedure and is stuck.
You might also restart the server (if possible) and drop it right away.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 10, 2003 at 10:21 am
If you do a sp_who2 to see what people are running, but inevitably it is an immediate snapshot and you may miss activity.
Suggestion is to use Profiler (Trace in old parlance) to continuously watch activity. You could also try DBCC opentran to ensure nothing was lingering.
You could use sp_depends to see if your objects were being required by another [calling] object that would explain why it was in use.
If not in sysdepends you could try
select distinct object_name(id) from syscomments where text like '%p2_view_filter_share1%'
If you set db to dbo-use-only that might prise off the klingons. Otherwise a MSSQL service bounce might be required if it has got confused [schedule when no other activity running]
Dick
London, UK
April 10, 2003 at 11:06 am
Thank You Steve and DickBaker.
I donno why it had happend. I could not see any connection which is being used in the Monitor tool of DBArtisan. I killed all the sleeping connections as well. Still no use.
I had restarted the SQLServer, SQLAgent & MSDTC Services on the server. Then dropped these procedures right away. Success!!! <img src=icon_smile_big.gif border=0 align=middle>
.
April 10, 2003 at 11:08 am
By the way... This is running on SQL Server 6.5.
.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply