Post reply

When a Sproc or Table was Last Used

  • September 21, 2005 at 7:16 am

    #168517

    How can I determine when a sproc or table was last used?

    I suspect that there are  many obsolete tables and sprocs in my database but how can I find out for sure??

    Thanks,

    DL

  • September 21, 2005 at 10:46 am

    #591898

    SQL server does not keep any logs for this purpose.

     

  • September 22, 2005 at 2:51 am

    #592119

    If you can justify the time you could amend each of the sprocs to append a line to a logging table - sproc name, getdate(), user.

    --
    Scott

  • September 23, 2005 at 9:54 am

    #592644

    The best you can do (that I've ever heard of) is to write a trigger that logs when an insert, update, or delete hits a table. There's no way (again, that I've ever heard of) to track or log selects against a table.

    For stored procedures, you could modify them update a logging table whenever they're called.

    For either, the killer is: how long do you watch until you are convinced that they're not being used? An hour? A week? A month? I'd hate to delete a procedure only to learn it's critical to the year-end accounting routines...

       Philip

     

  • September 23, 2005 at 10:10 am

    #592647

    Thanks everyone for your help.

    I wrote code that appends information to a log table and inserted it in each sproc. This has been working nicely. I suppose I can change the names of the tables I am suspicious of and change them back if necessary. I would be the first to hear of any problems that may arise.

    The end of year is approaching shortly and I can make the necessary deletions in January after end of year processing is run.

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

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