Post reply

How to check last usage date for SQL db objects?

  • March 15, 2007 at 10:42 am

    #62137

    In SQL 2000, is there any way to identify when last time database object was touched – select/insert/delete/update?   I have tones of stored procs, tables, views, etc. and I know that not all of them are used.   There are some old legacy crap and I want to clean that up.  I want to identify those that are not being used in last month or so, based on that I can communicate to my application teams to see if they can be archived or deleted off of the database. 

     

    Thanks

     

     

     

  • March 15, 2007 at 12:16 pm

    #695321

    SQL Profiler.

  • March 15, 2007 at 12:38 pm

    #695324

    How intrusive (or permormance affective) would it be to run SQL Profiler for the entire day to capture SQL Objects name?  I am not sure if its going to affect the performance on the production box.   

  • March 15, 2007 at 1:58 pm

    #695342

    There's no good way to determine when stored procedures have been used. Profiler works or if you can "touch" the stored procedures, you can add a line of code to log when they run.

    To see if tables are being changed, you could grab an old backup (week or month) and than restore to a new db and use a diff tool to see if data has changed. Could do it manually, but not sure it's worth saving $300.

    For queries, there's not a good way. You can take one of two methods. Use Profiler and hope you can identify access by going through the logs (load htem into a table and query them), or you can remove rights to the table/view/proc and see who complains.

    Be careful of this because one of the big problems is that there might be a proc or view that is used once a quarter or once a year for some periodic process. You might not catch that in a month or so with a monitoring process.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • March 16, 2007 at 1:53 pm

    #695538

    I had profiler running saving to a file for 2 months collecting over 15 million stored procedure calls, no visible effect on performance.  I imagine if your server is closed to being maxxed out before you do it it could become a problem. 

     

    My run was on SQL2005 not 2000.  Not sure if that has any bearing on performance or not.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply