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 (http://blog.SQLAuthority.com) , BOL – sys.dm_exec_query_stats, BOL – sys.dm_exec_sql_text

60 thoughts on “SQL SERVER – 2005 – Last Ran Query – Recently Ran Query

  1. Pingback: SQL SERVER - 2005 - CROSS APPLY Journey to SQL Authority with Pinal Dave

    • Hi dave,

      i have a doubt not regarding above query,

      Unfortunately i have deleted one function through Object explorer.
      even i don’t have backup for my database.

      How can i roll back recent deleted functions.

      hope i am clear.

      thanks
      srinu.

      Like

  2. Dave, cool script. Anyway (without running profiler constantly) we can go back (say for security reasons) and see what queries were run a day or so ago? You can email directly, too.

    Thanks,
    Brandon

    Like

  3. I was curious about this so I tried it & found the results a little difficult to believe. It shows that every fraction of a second, I’m running the create command on a stored procedure that already exists… as if I’m constantly trying to create it..but I think the SP is just executing. Thoughts?

    Like

  4. Hi Mate
    It gives last ran queries, If an SP is ran it will give the complete syntax of the SP, But not the execution of it. e.g

    CREATE PROCEDURE [dbo].[spTest]
    @myParam VARCHAR(100)

    BEGIN

    — SOMETHING

    END

    it would be very nice if WE COULD HAVE something like

    exec spTest @myPara = ‘abc’

    I am asking more or less like a SQL Server profiler trace.

    Please contact on lukegaroon@gmail.com

    Like

  5. Nice post. But i wanted to know which alert transaction has been applied on the particular table on which date.

    I tried
    select *
    from ::fn_dblog(null, null)
    where Operation = ‘LOP_MODIFY_COLUMNS’

    but it does not provide information regarding which column altered.

    Can any one help me?

    Like

  6. Great code! For anyone trying to use it make sure it run it against the MASTER database.

    BUT, Is there anyway I can get it to go back farther? It seems to only display records since the last time I booted.

    Like

  7. How to rollback last recently ran query in SQL Server. If we had not mentioned rollback to Savepoint or begin Trasaction methods. Is there any alternative.

    Like

  8. Can we find out what were last 100 queries that got executed on a perticulare DB. The query that you wrote will return those queries that are in memory.

    Like

  9. Pingback: SQL SERVER – Recently Executed T-SQL Query Journey to SQL Authority with Pinal Dave

  10. 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

    Like

  11. 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?

    Like

  12. 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

    Like

  13. 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

    Like

  14. 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

    Like

  15. 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

    Like

  16. 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

    Like

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

    Like

  18. 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

    Like

  19. 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 ?

    Like

  20. 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…

    Like

  21. 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

    Like

  22. hello…
    I have executed a query yesterday, but i don’t know which query i have ran. So, can you tell me where could i got the ran query log in sql server 2008.

    Like

  23. Good day, sir.

    I have created a query that makes use of CROSS APPLY. I was able to successfully create it. My problem is that when I tried to integrate it to Delphi dataset, it’s giving me an error when I try to activate the dataset. It seems that Delphi doesn’t want CROSS APPLY because when I try to remove the CROSS APPLY from my SQL code and run it again, it does not give any error at all. By the way, I’m using Delphi 7.

    I hope to read enlightenment. Thank you.

    Like

  24. Pinal, Thanks for this query.

    Is it possible to get more than one day’s data for the same. By this query I am getting today’s data. Old executed queries are not coming in the recordset. Is there any way to get it?

    Thanks,
    Shaiju CK

    Like

  25. Pingback: SQL SERVER – Weekly Series – Memory Lane – #010 « SQL Server Journey with SQL Authority

  26. DECLARE @EndTime DATETIME
    DECLARE @StartTime DATETIME
    SET @StartTime = GETDATE()
    Select * From Test
    SET @EndTime = GETDATE()

    PRINT ‘StartTime = ‘ + CONVERT(VARCHAR(30),@StartTime,121)
    PRINT ‘ EndTime = ‘ + CONVERT(VARCHAR(30),@EndTime,121)
    PRINT ‘ Duration = ‘ + CONVERT(VARCHAR(30),@EndTime,114) + ‘(hh:mi:ss:mmm)’

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s