January 2, 2011 at 9:05 pm
I need To identify Unused Store Procedure in my application and i need to eliminate that Store Procedure. How to eliminate
January 2, 2011 at 9:54 pm
Well, you could use this query to find out what is not in ProcedureCache, but you would have to be sure that the coverage of the application has taken into consideration all the procs that would have been executed are still in cache, but it is a place to start.
select SP.name, SP.create_date
from sys.syscacheobjects SO
right join sys.procedures SP ON SO.objid = SP.object_id and SO.objtype = 'Proc'
WHERE SO.objid IS NULL
January 2, 2011 at 10:50 pm
Happy New Year 2011..!!
Hi..Pls have a look at this look
January 3, 2011 at 6:43 am
there is no perfect mechanism for this. It's far too easy to have a proc that is only executed once a year or less that won't show up on any of the monitoring that you can do against active procedures. The best approach is to compare the app code, reporting code, and any ad hoc tsql sources against your existing stored procedures. Then, don't delete them. Rename them or put them into a storage schema for a year. If no errors have occured after that, then you can delete them.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 3, 2011 at 7:02 am
I think that we all agree that this is something that can be important, but there are many ways to get at information that will tell you what has been executed recently. You really do have to get inside the application and work with developers to ensure that the code necessary to the application is available to it.
I agree with the storage schema idea so that you don't delete it and then want it back. I use source control so that in the case that something gets deleted, I can get it back. It may be different for different organizations.
But I think that we gave you some good ideas on how you can find what has been executed.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply