Recently, I got into some discussions on deprecating stored procedures from the production environment. If you want to remove a proc from your production database, what are the steps you take to accomplish that?
The act of actually removing a proc from your prod database is trivial in itself. What is not so trivial in many real-world production environments is to make sure that the proc is really not being used. In other words, it's not enough to just consult some know-it-all guru, or check a hopefully-up-to-date application doc. The guru most likely does not know it all or may have already left to pursue some other opportunities, and the documentation is probably not up to date or may not even exist.
You need to have in place a robust process to determine if a proc is being used in your production environment.
There are multiple ways to arrive at that determination. If you have a 3rd-praty audit tool, you may use it to record the proc execution. Alternatively, you can always set up a trace and filter on the proc name or proc id. In addition, you can regularly query the plan cache to see if there is a cached plan for the proc.
In practice, I’ve found the querying-plan-cache method to be simple and inexpensive in terms of the computing resource consumption, and if you query the plan cache frequently (say, once every minute or so), it is reliable enough that the chance of missing a proc--that is in use but doesn't stay long enough in the plan cache--is quite small. For most stored proc deprecation purposes, I’d recommend this method over setting up a trace. Note that most, if not all the 3rd-party audit tools set up traces behind the scene.
But that’s just my experience. I wonder how you deal with this issue in your practice.