How many times we have wondered what were the last few queries ran on SQL Server? Following quick script demonstrates last ran query along with the time it was executed on SQL Server 2005.
SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC
Reference : Pinal Dave (https://blog.sqlauthority.com) , BOL – sys.dm_exec_query_stats, BOL – sys.dm_exec_sql_text
65 Comments. Leave new
Is there any way to see the last ran queries in MS SQL 2000? I couldn’t find it.
Hi
One of my table column is updated randomly and the value got changed all of a sudden. But after sometime, it may work fine. The change is constantly happening on a specific column of a specific table. Is there any way to find out which query caused that update?
Hi ,
How to get last updated row and its values for a particular table .
Thanks
Seshu
Hi,
I ran the code
SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC
but got
Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near ‘APPLY’.
any ideas?
ran it in management studio
Note that this will work from version 2005 onwards only
A very useful query. Will it be possible to filter by machine as well?
Not possible until you audit it
Please let me know how to get last query ran in SQL Server 2000
thanks…
penal dave
how to use
cross apply in sql
thanks a lot……
SQL Server help file has code example for cross aplly. Refer it
Dave,
Thank you for the tip about the last ran queries. How ever, how can I view only USERS activity ?? not the system activity or the SQL Agent activity ?
I know that SQL Server Profiler can do the job but I would like to know the code behind…
Thanks !
Doron
Hi
I need to insert last executed query with user but user will come via UI(.net code). Please let me know how can I manage?
Rrds,
Inder
how we use sql server2005? where we write query and run it because there is no query analyzer option……. plz tell me the whole process stepwise
See again there will Sql Management studio
how we use sql server2005? where we write query and run it because there is no query analyzer option……. plz tell me the whole process stepwise
how to verify last query executed in query analyser
Hi All,
I need some information about SQL SERVER “DINALI” and differences Btn 2008 & 2011
Regards,
Basava
**********Go for this it will update last ran query with username*********
SELECT c.session_id, s.host_name, s.login_name, s.status
, st.text, s.login_time, s.program_name, *
FROM sys.dm_exec_connections c
INNER JOIN sys.dm_exec_sessions s ON c.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS st
ORDER BY c.session_id
Is is possible to get this as Database category ????????
Hi All,
I wrote and tested one complex query 2 days back. today suddenly my system got restarted due to technical issue. So, Now any idea to get my previous complex query… Please help…
AnilJayanti
sir i worked in oracale 9i.In tha all query all dispalying in smae window but in microsoft sql it cnt.what to do?
first time i am working in microsoft sql thats way sir. if i want to display all previous query in same window what to do ?
Hello Pinal,
I want to track store procedure and its executed time by trigger.
When the store procedure is executed by application it should insert the store procedure name and its execution time in one table.
Reply soon…
Hello,
I run the query but the run I did yesterday is not showing … it was run within the “New Query” box interactively… anyway to trace it? to see if it was successfull?
Thanks,
Dom
This query was a life saver thanks