Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2006
    Posts
    3

    Question Unanswered: Is there a way to know the last execute date of a stored procedure?

    Is there a way to know the last execute date of a stored procedure? I would prefer not to change each of the thousands of procedures to log the their usage.

    I have inherited an old database with lots of users over the years. It has a lot of procedures and tables lying around which I believe aren't used that would be good to remove.

    Thank you,
    Lev Zaks

  2. #2
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    The last used/accessed times for tables (and indexes) can be found in monOpenObjectActivity (requires enabling the MDA tables).

    However, there is currently no way that I am aware of to see what procs haven't been used in a while. The (monProcedureCache) MDA table can give some information (if you find a proc in the cache then it's been used fairly recently :-) but it's obviously not good enough for your needs...

    UPDATE: That should be the monCachedProcedures table, above.

    Michael

  3. #3
    Join Date
    Sep 2006
    Posts
    3

    Not quite enough...

    Thanks for the reply, this will be helpful to get rid of some unused tables. But some of the procedures aren't called every day, so I am going to have to setup something more persistant then cache.

    Thanks.
    Lev

  4. #4
    Join Date
    Jul 2003
    Location
    Des Moines,IA
    Posts
    21
    Try using you can see top 20 procs used.
    dbcc traceon(3604)
    go
    dbcc memusage
    go


    see at the it might help run this when there no much activity on your server.

    See in very bottom of the output it will show all latest proces used & memory used.I hope i am right , Michael correct me if i am wrong.

    see this link for more info
    http://www.faqs.org/faqs/databases/sybase-faq/part9/

    Sreeni.
    Sreeni
    Sr.DBA in SYBASE
    Wellsfargo Bank,
    Des Moines,
    IA

  5. #5
    Join Date
    Sep 2006
    Posts
    15
    Try enabling auditing with minimal log.

    sp_audit 'exec_procedure','all','all','on'

    This will only audit executed Stored Procedures.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •