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

ross gravatar image

ross
11 1 1 1

And then the individual elements in my_emp_record are my_emp_record.empid and my_emp_record.ename?
Feb 02, 2010 at 03:39 PM KillerDBA
Thank you for your answer :)
Feb 03, 2010 at 01:18 PM OracleApprender
(comments are locked)
10|1200 characters needed characters left

4 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

Paul Ward gravatar image

Paul Ward
109

Wow, amazing how many of these questions are answered there. The text of the questions seems almost verbatim from the FAQ anyway :(
Feb 03, 2010 at 02:10 PM dmann
When looking into something else, I had run across the OraFAQ but hadn't taken notice of the... ummm... surprising relationship between questions here and there. I do believe I'll bookmark that site.
Feb 03, 2010 at 03:07 PM KillerDBA
I too don't know that it is from the PL/SQL FAQ's. I got few of this type of questions from my team mates.. Any how the link is very much useful to know elaborately about various other points too.
Feb 04, 2010 at 06:51 AM OracleApprender
(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

Laerte Junior gravatar image

Laerte Junior
488 2

This works as long as the query is still in cache. If it's been flushed out of cache, there's no way to know what's been run without some other process in place.
Oct 23, 2009 at 03:21 PM Grant Fritchey ♦♦
tried it on my ReportServerDB in SQL 2008 - did not get any data back
Oct 23, 2009 at 03:22 PM Raj More
well minded Grant, thans a lot !!!. But it was an option with environment information posted
Oct 23, 2009 at 03:39 PM Laerte Junior
I use something similar to display physical and logical read/write, elapsed time etc to find the procedures that consumes the most resources ant time.
Oct 23, 2009 at 04:03 PM Håkan Winther
(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

Dave 2 gravatar image

Dave 2
1

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x407
x343
x126
x6
x3

asked: Oct 23, 2009 at 02:34 PM

Seen: 30006 times

Last Updated: Oct 23, 2009 at 03:23 PM