October 2, 2014 at 12:58 am
How to find unused strored procedures in sql server, one my custeromer asking need to delete unused stored procedured, we are having more than 400 stored procedures .. any scripe or query
Please anyone help to us..
Thanks in advance
regards,
jerry
October 2, 2014 at 1:16 am
This is very difficult to determine. You could have stored procedures that only run once a year. If you remove such one, a process will fail when you've forgotten you had removed the stored procedure.
Besides a little bit of storage a stored procedure on itself doesn't have much impact on the system. I doubt if it is worth the effort....
But the way to go is:
- scan the code in the application for the called stored procedures. Also look for jobs, nested stored procedures, reporting, etc.
- add logging to the stored procedures that will store the last run date in a table/file
- run a profiler/Extended Events to see which stored procedures are executed
But remember: the above options only give you the stored procedures that are used. This doesn't imply all other stored procedures are absolete!
October 2, 2014 at 6:20 am
And if you do determine that a procedure is probably not used, you would be wise to rename that procedure for a period of time rather than delete it. This allows a quick recovery if it someone does try to use it at some point.
We use a naming convention of DelAfterYYYMMDD
spGetInvoice
spGetInvoice_DelAfter20150131
This let's us know when the object can be deleted, so we don't have to try and remember.
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply