Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 745
Steven Williams Avatar asked Sep 25 '08 00:09

Steven Williams


People also ask

How can I see last stored procedure in SQL Server?

Open the desired log file with SQL Server Profiler. You can see the stored procedure name in the ObjectName column and the name of the user who modified the stored procedure name in the LoginName column.

How can you tell if a stored procedure has been executed successfully?

Return Value in SQL Server Stored Procedure In default, when we execute a stored procedure in SQL Server, it returns an integer value and this value indicates the execution status of the stored procedure. The 0 value indicates, the procedure is completed successfully and the non-zero values indicate an error.

How do you find the last time a stored procedure was executed?

The type_desc column includes the object type while the execution_count column shows the number of times the stored procedure has been executed since it was last compiled. This can be useful information when researching performance issues.


1 Answers

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]

like image 52
Ben Hoffstein Avatar answered Sep 29 '22 11:09

Ben Hoffstein