x

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.

more ▼

asked Oct 23, 2009 at 02:34 PM in Default

avatar image

ross
11 1 1 3

(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

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.

more ▼

answered Oct 23, 2009 at 03:27 PM

avatar image

Paul Ward
109

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Oct 23, 2009 at 02:59 PM

avatar image

Laerte Junior
488 2 4

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Nov 08, 2009 at 09:17 PM

avatar image

Dave 2
1

(comments are locked)
10|1200 characters needed characters left

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 <YourDatabase>
 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())) = '<YourDatabase>'
 order by b.name;
more ▼

answered Mar 05 at 07:18 PM

avatar image

SteveArmistead
0

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x454
x408
x131
x3
x3

asked: Oct 23, 2009 at 02:34 PM

Seen: 52347 times

Last Updated: Mar 05 at 07:18 PM

Copyright 2016 Redgate Software. Privacy Policy