February 5, 2009 at 2:45 pm
Hi,
I am using this to get the last time a stored procedure was run
[font="Courier New"]use MyDB;
select top 22 t.text, st.last_execution_time, db_name(dbid) as DBName,
object_name(objectid) as ObjectName,
st.sql_handle
from sys.dm_exec_procedure_stats st cross apply sys.dm_exec_sql_text(st.sql_handle) t
join sys.dm_exec_requests r
where t.objectid is not null
and db_name(dbid) ='MyDB'
and text like '%MyStoreProcedure%'
order by st.last_execution_time desc[/font]
How can I get a non-null login id for that sql_handle ???
Tried sys.dm_exec_requests, but sql_handle does not match.
Please help!
February 5, 2009 at 3:00 pm
Do different people connect to the database with different credentials, or do they use a web page that passes a single set of credentials? (The later is much more common.)
As far as "last time such and such was run", I usually keep a server trace running in the background so that I can audit that kind of thing. That's the most reliable way of doing that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 5, 2009 at 3:06 pm
Different people may execute this sp using different SQL server logins.
I just need to know which ones ...
February 6, 2009 at 7:02 am
I use a trace for that.
The views only hold onto data for procs that have been cached, and flush and reload every time the execution plan changes. That means your handles and such might get out of synch pretty easily.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 26, 2009 at 10:02 am
FYI: when I tried the query listed. I go this msg below, any idea what went wrong? Thanks.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'where'.
March 26, 2009 at 10:38 am
comment out
join sys.dm_exec_requests r
,
then it'll work on SQL 2008
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply