Last Time Executed for a SQL Server Stored Procedure

SQL Server

Sometimes you need to know when a specific stored procedure was last executed. The following query will assist in telling you the last execution time of your stored procedures. We are assuming the query hasn’t been flushed from the cache. The will only work with SQL Server 2008 and higher.

SELECT object_name(m.object_id), 
       MAX(qs.last_execution_time) 
FROM   sys.sql_modules m 
       LEFT JOIN (sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) st) 
       ON m.object_id = st.objectid 
       AND st.dbid = db_id() 
GROUP BY object_name(m.object_id);

Another way is to manually script a logging event in the stored procedures you wish to track and insert the information in a custom logging table.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s