How to remove a Stored Procedure in SQL Server production environment? How can we be so sure that is not being used any more?

You need to know every single path from where this procedure is initiating before you can actually remove it from production. Following are some ways to determine the usage of a procedure in SQL Server.

  1. Set up a trace and filter through SQL Server profiler service on procedure name and ID.
  2. Another way is to regularly check procedure plan cache to see if there is a cached plan for the procedure.
  3. Alternatively you can use a 3rd party audit software to retrieve procedure executions. (Most of these soft wares use tracing at the back end)\