June 26, 2009 at 4:05 am
In my database there are so many unused code.
I want to audit this procedures functions or table on daily and monthly basis so after some duration i can age out it from my database.
Any one has script or some ideas how to perform this task?
June 26, 2009 at 4:35 am
It is really difficult to identify unused objects.
I applied many strategies and I still need to combine them all to be quite sure when I move away unused objects.
1) I added a logging instruction in every sproc
2) I coded a program to look for calls/reads for every object in:
a) stored procedures
b) functions
c) views
d) program(s) code
e) reports
f) jobs
g) DTS
h) SSIS packages
3) I frequently remove dead code from the programs to avoid false matches
4) I look at dependencies in SSMS
When I find dead objects, I move them in a tmp schema and I keep everything at least 6 months: afterwards I drop everything and keep the scripts.
Hope this helps
Gianluca
-- Gianluca Sartori
June 26, 2009 at 5:25 am
' I added a logging instruction in every sproc'
how can i add logging instruction in every objects like when it is used or executed then i know that.
June 26, 2009 at 6:15 am
I had to code an application to do that. The app got through all sp and added a call to a logging procedure as first instruction.
-- Gianluca Sartori
June 26, 2009 at 7:42 am
It's better to go through the application and list out the objects used in the application and remove other objects from the database. And make a practive of creating the unwanted tables in some naming convention. Because there might be some table or sp which are not used for some time but still referred in the application.
---------------------------------------------------
Thanks,
Satheesh.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply