Steven Williams
Steven Williams

Reputation: 1014

Last time a Stored Procedure was executed

On Sql Server 2000, is there a way to find out the date and time when a stored procedure was last executed?

Upvotes: 18

Views: 38510

Answers (2)

Ben Hoffstein
Ben Hoffstein

Reputation: 103385

If a stored procedure is still in the procedure cache, you can find the last time it was executed by querying the sys.dm_exec_query_stats DMV. In this example, I also cross apply to the sys.dm_exec_query_plan DMF in order to qualify the object id:

declare @proc_nm sysname

-- select the procedure name here
set @proc_nm = 'usp_test'

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('AdventureWorks')) = @proc_nm 

[Source]

Upvotes: 24

Danimal
Danimal

Reputation: 7710

Not without logging or tracing, I'm afraid

Upvotes: 9

Related Questions