SQL SERVER – 2005 – Last Ran Query – Recently Ran Query « Journey to SQL Authority with Pinal Dave

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

January 3, 2008 by pinaldave

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

Posted in Pinal Dave, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL System Table, SQL Tips and Tricks, T SQL, Technology | Tagged SQL DMV | 18 Comments

18 Responses

on January 3, 2008 at 2:36 pm parikh

is this possible in sql server 2000. Its urgent so please revert me asap. Any other way to know the same thing in sql server 2000.
on January 5, 2008 at 3:32 pm SQL SERVER - 2005 - CROSS APPLY Journey to SQL Authority with Pinal Dave

[...] 4, 2008 by pinaldave Yesterday I wrote article about SQL SERVER – 2005 – Last Ran Query – Recently Ran Query. I had used CROSS APPLY in the query. I got email from one reader asking what is CROSS APPLY. In [...]
on January 10, 2008 at 1:54 pm Tommelorus

Useful query. Thanks.
on January 15, 2008 at 3:09 pm Jon Sayce

Hi,

I regularly found myself trying to remember recent queries, so I wrote an add-in to store them:

http://sqlblogcasts.com/blogs/jonsayce/archive/2008/01/14/Total-SQL-Recall.aspx

Jon.
on January 15, 2008 at 3:20 pm pinaldave

Jon,

Wonderful Add-in.

Thank you very much,

Regards,
Pinal Dave ( http://www.SQLAuthority.com )
on January 18, 2008 at 3:41 am BrandonMoser

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
on January 21, 2008 at 7:35 pm sen

Can we get the userID under which the SQL statements were executed?

Thanks
Sen
on February 5, 2008 at 12:41 pm craig

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?
on April 20, 2008 at 9:40 pm Vaibhav

Hi,

I am also interested in tracing out from which host address the statement got executed.

Thanks,
Vaibhav.
on May 29, 2008 at 10:53 pm Jorge

Hi!!

This query can be made in SQL-SERVER 2000?
on August 20, 2008 at 3:57 pm Palani

Hi

I was searching so long time after i got your article.

its really wonderful.

Thanks & Regds
Palani
on November 7, 2008 at 6:32 pm Haroon

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
on December 29, 2008 at 2:50 pm penks

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?
on February 17, 2009 at 4:20 am Vander

Can anyone please tell me how to do this in sql 2000. Having problems with “Cross Apply”
on April 1, 2009 at 11:14 pm Matt

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.
on July 4, 2009 at 12:22 pm Ankur

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.
on July 6, 2009 at 1:05 am Imran Mohammed

@Ankur,

Point in Time Recovery ( If you have your database in Full Recovery Model)

~ IM.
on July 8, 2009 at 4:08 pm Ankur

Thanks for response