SQL SERVER – 2005 – Last Ran Query – Recently Ran Query

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

Quest

SQL DMV, SQL Scripts, SQL System Table
Previous Post
SQLAuthority New – Best Practices for Speeding Up Your Web Site
Next Post
SQL SERVER – Quick Note on CROSS APPLY

Related Posts

65 Comments. Leave new

  • Hi
    Can we find out what were last 100 queries that got executed in SQL Server 2000. When I executed same query got error.
    Line 4: Incorrect syntax near ‘APPLY’.
    Thanks

    Reply
  • Is there any way to see the last ran queries in MS SQL 2000? I couldn’t find it.

    Reply
  • 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?

    Reply
  • Hi ,

    How to get last updated row and its values for a particular table .

    Thanks
    Seshu

    Reply
  • 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

    Reply
  • A very useful query. Will it be possible to filter by machine as well?

    Reply
  • Please let me know how to get last query ran in SQL Server 2000

    Reply
  • thanks…
    penal dave
    how to use
    cross apply in sql

    thanks a lot……

    Reply
  • 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

    Reply
  • 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

    Reply
  • preeti jaiswal
    April 23, 2011 3:12 am

    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

    Reply
  • preeti jaiswal
    April 23, 2011 3:13 am

    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

    Reply
  • how to verify last query executed in query analyser

    Reply
  • Hi All,

    I need some information about SQL SERVER “DINALI” and differences Btn 2008 & 2011

    Regards,
    Basava

    Reply
  • Purush otta ma
    July 26, 2011 2:15 pm

    **********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

    Reply
  • Is is possible to get this as Database category ????????

    Reply
  • 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

    Reply
  • 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 ?

    Reply
  • 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…

    Reply
  • g2-902125243db7d5e697a20ae0137565ac
    February 7, 2012 10:47 pm

    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

    Reply

Leave a Reply