Is there a way in SQL I can tell when the last time a stored procedure was run or a table was accessed? I know there are a lot of objects in my system that are probably no longer being used but what is the best way to go about identifing these objects?
Gk
That is not going to be a 'simple' process. There is nothing inherrent in SQL Server that will give you that information with certainty.
You could set up a Profiler trace to capture the procedure/function/view/table name over time.
There are some Third party products that will follow the dependency trees, and also track usage. Check Tibor's list.
The process I follow is this:
When I identify an object that is a removal candidate:
All Stored Procedures/Functions/views/Tables that have been created in a database for solely administrative purposes are, in SQL 2000 named [dbs_, dbf_, dbv_, db_] and in SQL 2005, added to the [Admin] schema. I have a 'home-grown' .NET tool that will cycle through the source control store, examining application code, compiling a list of command.text statements, Since my practice is to require the use of stored procedures for all data access, the command.text is a list of Stored Procedures. I then check object dependencies against the list. I now have my 'first pass' candidate list. I first rename the candidate object (add 'x' to the beginning and they all sort to the bottom of the list). If something then 'breaks', it is very quick to change the name back and restore functionality. I leave the renamed objects for 3-6 months before final deletion. (Some things may be rarely used, but they may be for an 'important' process -such as reporting.) If it is obvious that the object is for a reporting process, I may leave it for a year -there are annual reports... When an object is finally removed, I script it out and store the script in an object archive. (Who knows, perhaps that object had some 'nice' code in it that I can refer to later.)sql
No comments:
Post a Comment