ssms query log

Question

Hi Expert,

How to find ssms query log on last friday with all statement

Answer 1

The answer is that with plain vanilla SSMS you don't. SSMS does not maintain a query log.

However, there are third party tools out there. One such example is SSMS Tools Pack, developed by my MVP colleague and friend Mladen Prajdić.

Answer 2

Hi @Shambhu Rai ,
You can use SQL Server Profiler to check the history action that you have done. Like the following picture:

Or you can use Query Store in SSMS and the using way can be seen in this official article:https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver15

And you can use SQL Complete\Execution History pls check this article:https://www.devart.com/dbforge/sql/sqlcomplete/

Personally, I'm not recommeding the second soultion too much,but this solution can allow you to use T-SQL. The reason why I don't recommed is that the soultion is more to deal with performance.
Wish you good luck !

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment"

Answer 3

Hi @Shambhu Rai ,
Don't know whether you solved your problem or not. I've found a T-SQL, which can show the statement history that was cached, but it may not meet your demand that show all the statements and time shall be last friday. Anyway, I hope this can be choice that you may need in the future.

SELECT st.text as sql_statement,  
       qs.creation_time as plan_last_compiled,  
       qs.last_execution_time as plan_last_executed,  
       qs.execution_count as plan_executed_count,  
       qp.query_plan  
FROM sys.dm_exec_query_stats qs  
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st  
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp  
order by total_elapsed_time/execution_count desc  

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment"