Is there any way that I can tell when a stored procedure was last executed or who executed it? Is there any execution history that I can look at?
Thanks.
asked
Can I tell when a stored procedure was last executed or who executed it?
This will get you half way there:
Use MyDatabase Go select b.name, a.last_execution_time from sys.dm_exec_procedure_stats a inner join sys.objects b on a.object_id = b.object_id where DB_NAME(a.database_ID) = 'MyDatabase'
It will tell you the last time a stored proc was executed.
As for who ran it, as far as I know that is not recorded. You could use profiler to recored this, but i wouldn't do that on production server.
Try This
SELECT a.execution_count ,
OBJECT_NAME(objectid) Name,
query_text = SUBSTRING(
b.text,
a.statement_start_offset/2,
( CASE WHEN a.statement_end_offset = -1
THEN len(convert(nvarchar(max), b.text)) * 2
ELSE a.statement_end_offset
END - a.statement_start_offset)/2
) ,
b.dbid ,
dbname = db_name(b.dbid) ,
b.objectid ,
a.creation_time,
a.last_execution_time,
a.*
FROM sys.dm_exec_query_stats a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) as b
WHERE OBJECT_NAME(objectid) = 'YOURPROCEDURE'
ORDER BY a.last_execution_time DESC
See my demo code at: http://theseldonvault.blogspot.com/
The example stores the time. But you can easily extend it to store past parameters and who called the SP.
This has not been checked for performance impact.
Dave
This query is based off Paul Ward's query above, but includes all stored procs in the database. If a proc has not been executed since service restart, it will display 1/1/1900 for LastExecuted. This might be handy to ultimately identify procedures that are never executed. use go select b.name, isnull(a.last_execution_time,'01/01/1900') as ExecTime from sys.objects b left join sys.dm_exec_procedure_stats a on a.object_id = b.object_id where DB_NAME(isnull(a.database_id,db_id())) = ' ' order by b.name;
1 Person is following this question.
