Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2005
    Posts
    319

    Unanswered: Anyway to find out the last time a proc was called?

    Is there some new usage stats system table that I can query for 2005/2008?

    Boss asked me how long it took for us to redirect traffic off of one of our DB servers. I told him we typically need to be proactive and run a SQL profiler trace. Alternatively we could look at the web logs, he said that wasn't turn on (?).

    Anyway back to my original question, anyway to see the last time a proc was called from a system table? I know which procs I want to lookup.

  2. #2
    Join Date
    Jun 2005
    Posts
    319
    I came up with this, look good?

    Code:
     SELECT TOP 10 QS.*
     FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
     join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle
     WHERE DB_NAME(st.dbid) is not null and cp.objtype = 'proc'
     AND OBJECT_NAME(objectid,st.dbid) = '{myproc}'
     ORDER BY LAST_EXECUTION_TIME DESC

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Another place you could look would be the "black box" trace that is kept in the log directory of the SQL Server.

Posting Permissions

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