October 7, 2009 at 4:51 am
Respected All
Is there any way in sql server to find out all sql queries that ran in one day without running profiler
Please give me solution.
Thanks & Regards
Ghanshyam GUpta
October 7, 2009 at 5:11 am
You can query the default trace, but I am interested to see what ideas others have.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 7, 2009 at 5:49 am
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
..
October 7, 2009 at 5:54 am
I knew I could rely on someone else to post a query, you might want to restrict it though, as I do believe the op wanted it purely for the same day.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 7, 2009 at 6:13 am
Declare @date Datetime
Set @date=getdate()--Or give any date u want
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 dateadd(day, datediff(day, 0, deqs.last_execution_time), 0)=dateadd(day, datediff(day, 0, @date), 0)
ORDER BY deqs.last_execution_time DESC
..
October 7, 2009 at 7:28 am
Ta very much... 😀
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 7, 2009 at 7:38 am
sys.dm_exec_query_stats is what I would recommend as well, but remember that it only shows the queries that are currently in cache. If queries have been executed during the day but aged out of cache, then there won't be a record there of them having been executed.
You're better off using trace events.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply