Find Stored Procedure Last Execution Details

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Find Stored Procedure Last Execution Details

Author

Topic

learntsql

524 Posts

Posted - 2009-12-15 : 07:56:22

Hi...
How to find last Execution details of any specific stored procedure.
Details like:
Execution Datetime,
Host Name/IP Address,
Login name
etc....

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-12-15 : 09:06:04

1. build/create a log yourself for all executions of procedures.
or
2. use PROFILER to log activity.

Unfortunately both will only address future executions of procedures....and may not address the IP/Login data required.

ajitgadge
Starting Member

12 Posts

Posted - 2009-12-22 : 07:49:42

--Please try below T-SQL if you have sql 2005.
select session_id
,connect_time
,[text] as Last_Executed_SQL
,auth_scheme as Auth_Type
,client_net_address
,client_tcp_port
from sys.dm_exec_connections SP
outer apply sys.dm_exec_sql_text (sp.most_recent_sql_handle) as sql_text
order by connect_time desc

---------------------------------

select
last_execution_time
, [text]
,last_elapsed_time

FROM sys.dm_exec_query_stats sp
outer apply sys.dm_exec_sql_text (sp.plan_handle) as sql_text
ORDER BY last_execution_time desc

learntsql

524 Posts

Posted - 2009-12-23 : 00:02:51

wonderful ajitgadge,
Veryuseful script.
I have one query can i mention Time period like if i want to capture
Yesterday's transactions done against any perticuler server.
Can we get MachineName?
TIA.

ajitgadge
Starting Member

12 Posts

Posted - 2009-12-28 : 08:22:08

Yes you can mentioned your machine host name or IP in where clause.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources