history of queries ran yesterday from a specific database in SQL server

Question

Hi ,
I am beginner and i just want list all the queries ran yesterday.
How can I find the history of queries ran yesterday from a specific database in SQL server

Answer 1

Hi @Sharon01 ,

When you need to review the data about executed SQL statements in SSMS for a particular period, you can use several options:

1. Queries are saved in the cache via system representations (sys.dm_exec_query_stats, sys.dm_exec_sql_text, and sys.dm_exec_query_plan)
2. Using SQL Server Profiler
3. Using Extended Events
4. Using the Query Store, starting from the 2016 version
5. Using SQL Complete (SQL Complete\Execution History) in SSMS

For more details , please see : https://blog.devart.com/sql-server-query-history.html

Best regards,
Niko

----------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

There is nothing built-in for this purpose. Jingyang mentioned Query Store, but the purpose of Query Store is to track performance. It tracks queries and their performance aggregated by the hour. However, with the AUTO setting, which is the default on recent versions, Query Store may not track one-time queries.

On the server side, you can achieve the above with Extended Events or Trace, but that is not really for the beginner.

On the client side there are extensions to SSMS the performs this task, for instance Mladen Prjadić's SSMS Tools Pack. Mladen charges a fee for the software, but can download a trial.

Answer 3

Turn the built-in Query Store on for your databases, you may find the information you need.