x

Last executed insert , update or delete query

How to find last executed insert, update or delete query in SQL Server 2008.

more ▼

asked May 04, 2011 at 01:34 AM in Default

avatar image

surve_sk
81 5 5 10

Thnx Kev, But it doesn’t work

insert into temp(id,name) values(1,'s')

select * from sys.dm_exec_connections decs CROSS APPLY sys.dm_exec_sql_text(decs.most_recent_sql_handle) dest where (dest.[TEXT] like '%INSERT%' or dest.[TEXT] like '%UPDATE%' or dest.[TEXT] like '%DELETE%'

 )

and decs.session_id <> @@SPID

May 04, 2011 at 02:08 AM surve_sk

remove the last where clause - that is ignoring this session - I didn't think you would generally be looking for executions within the same query window. Alternatively, execute the insert in another window

May 04, 2011 at 02:10 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

One option is to query the Dynamic Management Views. This snippet assumes that SQL hasn't been restarted, that the connection that executed is still alive, and that we want to ignore the connection executing this code..

 select * from sys.dm_exec_connections decs
 CROSS APPLY sys.dm_exec_sql_text(decs.most_recent_sql_handle) dest
 where 
     (dest.[TEXT] like '%INSERT%'
     or
     dest.[TEXT] like '%UPDATE%'
     or
     dest.[TEXT] like '%DELETE%'
     )
 and decs.session_id <> @@SPID


Ideally you wouldn't use select *, but choose the columns from the DMVs that you are interested in.

more ▼

answered May 04, 2011 at 01:54 AM

avatar image

Kev Riley ♦♦
66.2k 48 63 81

(comments are locked)
10|1200 characters needed characters left

The best way to do this is to setup a server side trace or monitor with extended events. Anything else is going to be guessing. Kev's answer is great if you meet all the requirements, but most systems will put the connection back into the connection pool as soon as the query is finished and you won't see what's needed.

You could try running a query against sys.dm_exec_query_stats and order by the last_execution_time. Assuming the query is still in cache, you'll see the most recent query, regardless of what it is, the last time it was executed.

more ▼

answered May 04, 2011 at 04:30 AM

avatar image

Grant Fritchey ♦♦
137k 20 44 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x440
x57
x7

asked: May 04, 2011 at 01:34 AM

Seen: 3606 times

Last Updated: May 04, 2011 at 01:42 AM

Copyright 2017 Redgate Software. Privacy Policy