SQL Server Performance

Check when a stored procedure last ran

Discussion in 'SQL Server 2008 General DBA Questions' started by WingSzeto, Apr 15, 2009.

  1. WingSzeto Member

    Is there a way in SQL 2005/2008 to find out when a sp was last run or if it has been ever run before other than using profiler or creating some logging code ?
    wingman
  2. MohammedU New Member

    It is very difficult ...but if your db recovery model is full and you have the log backups then you can fn_dblog function to read the log to find some info...
    or check sys.dm_exec_cached_plans and sys.dm_exec_query_stats DMVs...if you are nor restarted your sql after procedure execution you should see a row...
    SELECT OBJECT_NAME(sys.dm_exec_sql_text.objectid),

    sys.dm_exec_query_stats.*

    FROM sys.dm_exec_query_stats

    CROSS APPLY sys.dm_exec_sql_text (sys.dm_exec_query_stats.sql_handle)

    WHERE sys.dm_exec_sql_text.dbid = db_id()

    AND OBJECT_NAME(sys.dm_exec_sql_text.objectid) = '<Stored Procedure>'

Share This Page