Sometimes on the system that's with different SQL server database that includes a lot of many years of development onto it. It's not huge in data volume (a couple of GB) however it provides extensive complexity (100s of tables, 100s of saved methods).

I wish to begin by cleaning up the items that is not used anymore. There exists a weekly/monthly/quarterly/annual cycle which will imply that some stuff won't be employed for annually. However, if there's a narrow your search of stuff that haven't been employed for a couple of several weeks, we may have the ability to decide by inspection if they're still helpful.

My intention would be to start logging use of all of the database objects. I have got a bit of ideas but what I would like is really a log will be able to become a listing of stuff that aren't getting used. I have a couple of ideas but I would like some expert help.

edit : I'd like to obvious up will be able to I must begin to see the accessibility tables/sights along with the saved procs and processes.

This can be a similar question, particularly asking when saved methods were utilised: http://stackoverflow.com/questions/1207274/how-do-i-log-the-frequency-and-last-used-time-for-a-stored-procedure

You check [cde]: the final update is maintained within the sys.dm_db_index_usage_stats column and also the last Choose is maintained within the among last_user_update, last_user_seek or last_user_scan. Observe that the counters are totally reset at SQL Server launch, so you have to run the application and perform a thorough test of each and every feature to obtain relevant results.

For saved procedure you can start a server trace monitoring the the [cde] event. After your results are run that, again, exercise every feature from the product, steer clear of the trace and employ SQL aggregate functions to count distinct occurrences of procedure names in last_user_lookup within the trace file. You browse the trace with [cde].

Well SQL profiler could be my suggestion, but you'd have to be careful getting that running, since it does introduce a performance overhead if you have a trace running.