February 13, 2003 at 12:43 pm
Is there a way to determine when a database object (table, index, stored proc, etc) was last used or never used.
We have developers come and go and create db objects that get left behind. No one wants to delete them because they don't know if they are still in use or not. Would be nice to determine if they are actually used so that the outdated stuff can be cleaned up.
I found a posting for Oracle that showed how to do this for a Oracle database, can this be done for SQL Server?
February 13, 2003 at 1:14 pm
There is no way to do this for SQL except by backtracking the transaction logs with something like Log Explorer.
Steve Jones
February 13, 2003 at 1:17 pm
I can't say that this is the best way to do it, but what I do is profile the database, including objectid and indexid, over a period of time determined to encompass ALL calls made to the database by all parts of the app using it, and from that point, it's easy to create queries off the data for whats accessed when, how often, etc....Gives excellent demographics of usage of all database objects.....I use it for index analysis and table usage mostly, but I've traced down direct table calls the same way, and removing unused indexes can be a tremendous perofrmance boost...
February 13, 2003 at 2:52 pm
quote:
There is no way to do this for SQL except by backtracking the transaction logs with something like Log Explorer.Steve Jones
http://www.sqlservercentral.com/columnists/sjones
This still however does not address those called via select or other non-transaction means. Currently your best hope is to use Profiler.
However some folks have suggested renaming items one at the time and after a period if is not effected search all the other code for reference to it and rename those. Doing this systematically can speed things along when also using Profiler.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply