Solved

store procedure -  last execution

Posted on 2016-08-06
2
32 Views
Last Modified: 2016-08-10
Hi experts

it is possible to know when he was executed (date and time) for the last time a stored procedure
0
Comment
Question by:enrique_aeo
2 Comments
 
LVL 40

Accepted Solution

by:
Sharath earned 250 total points
ID: 41745990
check this.

http://sqlfool.com/2009/08/find-recently-executed-stored-procedures/

Select DB_Name(dest.[dbid]) As 'databaseName'
    , Object_Name(dest.objectid, dest.[dbid]) As 'procName'
    , Max(deqs.last_execution_time) As 'last_execution'
From sys.dm_exec_query_stats As deqs
Cross Apply sys.dm_exec_sql_text(deqs.sql_handle) As dest
Where dest.[text] Like '%yourTableName%' -- replace
    And dest.[dbid] Is Not Null  -- exclude ad-hocs
Group By db_name(dest.[dbid])
    , Object_Name(dest.objectid, dest.[dbid])
Order By databaseName
    , procName
Option (MaxDop 1);

Open in new window

0
 
LVL 13

Assisted Solution

by:geek_vj
geek_vj earned 250 total points
ID: 41746099
Please note that you can retrieve this information only from the available info from cache and there is no way to retrieve if the specified proc was flushed out of cache either by system or manually.

Here is the query:

declare @proc_nm sysname

-- select the procedure name here
set @proc_nm = 'SP name here'

select s.last_execution_time
from sys.dm_exec_query_stats s
cross apply sys.dm_exec_query_plan (s.plan_handle) p
where object_name(p.objectid, db_id('AdventureWorks')) = @proc_nm

-- Ref: http://stackoverflow.com/questions/130753/last-time-a-stored-procedure-was-executed

Please understand that this might give partial results as it will pull information only from the current cache.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now