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 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.
Useful query. Thanks.
Hi,
I regularly found myself trying to remember recent queries, so I wrote an add-in to store them:
Jon.
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
Can we get the userID under which the SQL statements were executed?
Thanks
Sen
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?
Hi,
I am also interested in tracing out from which host address the statement got executed.
Thanks,
Vaibhav.
Hi!!
This query can be made in SQL-SERVER 2000?
Hi
I was searching so long time after i got your article.
its really wonderful.
Thanks & Regds
Palani
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
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?
Can anyone please tell me how to do this in sql 2000. Having problems with “Cross Apply”
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.
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.
@Ankur,
Point in Time Recovery ( If you have your database in Full Recovery Model)
~ IM.
Thanks for response
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.
Hi Pinal,
It is wonderful……
thanks a lot….
Hi!
Is there any way to rollback last executed query?
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