How to find the last execution time of stored procedure sql server 2005

_{Thursday, June 13, 2013 3:02 PM}

Hi All,

I Want to find the last execution time of stored procedure sql server 2005.

Please reply soon .......????????????

Thanks

_{Thursday, June 13, 2013 3:08 PM ✅Answered}

Check this. It might be useful to you

http://stackoverflow.com/questions/130753/last-time-a-stored-procedure-was-executed

Known is a DROP, Unknown is an OCEAN.

_{Friday, June 14, 2013 10:12 AM ✅Answered}

Did you try the query?

select s.last_execution_time
from sys.dm_exec_query_stats s
cross apply sys.dm_exec_query_plan (s.plan_handle) p
where object_name(p.objectid, db_id('DBNAME')) = @proc_nm 

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

_{Friday, June 14, 2013 9:53 AM}

Hi,

Above link is used for sql server 2008 .  I am asking of sql 2005.

_{Friday, June 14, 2013 10:59 AM | 1 vote}

Above link is used for sql server 2008 .  I am asking of sql 2005.

The query Bagaaram and Lateesh referred to (using sys.dm_exec_query_stats) will work on SQL 2005.  I should add that SQL 2008 introduced a similar DMV (sys.dm_exec_procedure_stats), which is handy if you are only interested in proc stats and plans in later versions.

Dan Guzman, SQL Server MVP, http://www.dbdelta.com

_{Friday, June 14, 2013 11:04 AM}

You may be able to get a result from the sys.dm_exec_query_stats DMV, bearing in mind it gets reset on server restart, cache flush, etc so the proper answer here is to add logging to your stored procedures.

Rough pseudocode would be something like:

CREATE PROC dbo.usp_yourProc

AS

SET NOCOUNT ON

--Log proc start

--Do some work

--Log proc finish

--!!TODO Add error handler / log proc errors