Log usage of database tables, functions, stored procedures, views etc

Question

I work on a system that is based on a SQL server database that has a lot of years of development on it. It is not huge in data volume (a few GB) but it has a lot of complexity (hundreds of tables, hundreds of stored procedures).

I want to start by cleaning out the stuff that isn't used any more. We have a weekly/monthly/quarterly/annual cycle that will mean that some stuff will not be used for a year. However, if there is a short list of things that have not been used for a few months, we might be able to decide by inspection if they are still useful.

My intention is to start logging access to all the database objects. I've got some ideas but what I'd like is a log that I can turn into a list of things that aren't being used. I've a few ideas but I'd like some expert help.

edit : I'd also like to clear up that I can I would like to see the access to the tables/views as well as the stored procs and functions.

Answer 1

Well SQL profiler would be my suggestion, but you'd need to be careful having that running, because it does introduce a performance overhead when you have a trace running.

Answer 2

This is a similar question, specifically asking when stored procedures were used: http://stackoverflow.com/questions/1207274/how-do-i-log-the-frequency-and-last-used-time-for-a-stored-procedure

Answer 3

You check sys.dm_db_index_usage_stats: the last update is retained in the last_user_update column and the last SELECT is retained in the one of last_user_seek, last_user_scan or last_user_lookup. Note that the counters are reset at SQL Server start up, so you need to run your application and do a thorough test of every feature to get relevant results.

For stored procedure you should start a server trace monitoring the the SP:Starting event. After your tests are run that, again, exercise every feature of the product, stop the trace and use SQL aggregate functions to count distinct occurrences of procedure names in TextData in the trace file. You read the trace with fn_trace_gettable.