Retrieving Executed Queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Retrieving Executed Queries

Author

Topic

alex_808
Starting Member

16 Posts

Posted - 2010-06-16 : 09:54:44

Hello i would like to know if there is a way i can retrieve the list of all executed queries within a couple days.. I know about


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 this retrieves very rencent queries, to be more specific i would like to find a delete query that happened yesterday..

Thanks! :)

SD_Monkey
Starting Member

38 Posts

Posted - 2010-06-17 : 04:56:20

you can find it on the sql profiler.... i forgot the article but on the profiler you can trace you activity or other on it...

A maze make you much more better

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-06-17 : 05:32:51

Unless you were running a trace yesterday when the delete ran, there's no guaranteed way to see it. The plan cache has some queries, but queries can (and will be) flushed from the cache due to memory pressure, stats changes, object changes and the like.

--
Gail Shaw
SQL Server MVP

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-17 : 06:27:10

Depending on how important it is I think it's possible using some 3rd party tools to read the sql server logs. At least it used to be in older versions...

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-17 : 06:31:11

Here you go (I have never tried the product so I have no idea of its performance): http://apexsql.com/sql_tools_log.aspx

Seems like pricing starts at $999

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

oxegg
Starting Member

6 Posts

Posted - 2010-06-17 : 06:59:47

Hello!
You can use the SQL syntax to look for all the executed queries:
DBCC LOG(DatabaseName,2)
OK, Good Luck!

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-06-17 : 08:21:24

That won't show executed queries, it will show data changes.

--
Gail Shaw
SQL Server MVP

oxegg
Starting Member

6 Posts

Posted - 2010-06-18 : 03:29:04

Oh....Thanks for GilaMonster's amending!