question

ross avatar image
ross asked

Can I tell when a stored procedure was last executed or who executed it?

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.

sql-serverstored-proceduresadministrationcacheprocedure-cache
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Paul Ward avatar image
Paul Ward answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Laerte Junior avatar image
Laerte Junior answered

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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Dave 2 avatar image
Dave 2 answered

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

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SteveArmistead avatar image
SteveArmistead answered
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;
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.