Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to find out what delete and update statements have been executed on a certain database in a certain period of time.

I came across this following query, but it generates an error message:

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
where dest.TEXT like '%Delete%from%'
ORDER BY deqs.last_execution_time DESC

The error message is:

Msg 102, Level 15, State 1, Line 3 Incorrect syntax near '.'.

If I try:

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(sql_handle) AS dest
where dest.TEXT like '%Delete%from%'
ORDER BY deqs.last_execution_time DESC

I get another error:

Msg 321, Level 15, State 1, Line 3
"sql_handle" is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.

As far as I understand in my research of the topic it is not an easy task to achieve.

Is this query even the right approach?

SQL VERSION in question:

Microsoft SQL Server Standard Edition (64-bit)
Microsoft SQL Server Management Studio                      10.0.2531.0
Microsoft Analysis Services Client Tools                        10.0.1600.22
Microsoft Data Access Components (MDAC)                     6.1.7600.16385
Microsoft MSXML                     3.0 6.0 
Microsoft Internet Explorer                     8.0.7600.16385
Microsoft .NET Framework                        2.0.50727.4984
Operating System                        6.1.7600
share|improve this question

migrated from stackoverflow.com Mar 7 '13 at 9:05

This question came from our site for professional and enthusiast programmers.

1  
This SQL query is required to execute on [Master] database. –  Agilox Mar 7 '13 at 8:06
1  
@wachy - No it isn't but it requires to be run under a DB with the correct compatibility level. Changing the compatibility level of the user database to SQL Server 2008 would also work. –  Martin Smith Mar 7 '13 at 8:07
2  
This assumes the OP is actually on 2008. The version shown in the question is version of SSMS not server version. –  Martin Smith Mar 7 '13 at 8:09
2  
@Jester - No. The query just returns the plans that happen to be in the cache. If they were parameterised the actual run time values the plans were executed with or not stored anywhere. The best you can do is find the parameter values the plans were compiled with by looking in the XML for the plan (and you can get the XML by adding query_plan to the SELECT and CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS Q before the WHERE –  Martin Smith Mar 7 '13 at 8:25
1  
@Jester - No the machine name information is not stored. If you need this you need to set up some kind of auditing in advance it is not generally possible to get this retrospectively. –  Martin Smith Mar 7 '13 at 8:41

2 Answers 2

Run this in the context of the master database as you must be running it under the context of a user database set to SQL Server 2000 compatibility level.

The following should work fine

USE master;

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
WHERE  dest.text LIKE '%Delete%from%'
ORDER  BY deqs.last_execution_time DESC 
share|improve this answer

Man, you query works. I have SQL Server 2008 R2. The second one is also launchable

enter image description here

share|improve this answer
    
I have SQL Server 2008 NOT R2 and I get the above error –  Jester Mar 7 '13 at 8:00
    
Will try to restore the DB on the R2 server and see what happens –  Jester Mar 7 '13 at 8:01
    
And what is you version (Standart, Developer, Enterprise)? –  voo Mar 7 '13 at 8:02
    
Version info Added above –  Jester Mar 7 '13 at 8:05
    
I suppose, that the problem might be in version. I've changed the copability to 80, 90 and 100 - it worked. And USE master - is not nesessary, it works without it –  voo Mar 7 '13 at 8:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.