Search This Blog

Friday, November 8, 2013

When was the last time a stored procedure was executed?

To get this answer you need this query;

DECLARE @ProcedureName sysname
SET @ProcedureName ='YourSPName'

SELECT  @ProcedureName AS ProcedureName,
                 MAX(s.last_execution_time) AS LastTimeExecuted
FROM sys.dm_exec_query_stats AS s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS
WHERE Object_name(t.objectid, db_id('YourDBName')) = @ProcedureName

For details check the information on sys.dm_exec_sql_text and sys.dm_exec_query_stats on BOL.

No comments:

Post a Comment