How to enable sql query logging

Don75 81 Reputation points
2022-01-21T16:32:56.393+00:00

I need to get a list of queries and stored procedures that are run from an sql server for a given date range (usually the last 24 hours) for an auditing company.
I am not sure whether this is possible. Would appreciate if any one could help me with how to do this type of logging in sql server 2016.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,758 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,554 questions
Accepted answer
  1. Seeya Xi-MSFT 16,441 Reputation points
    2022-01-24T08:58:46.407+00:00

    Hi @Don75 ,

    Please see this blog. I think this will help you.
    How to Check SQL Server Query History
    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

    Best regards,
    Seeya

    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.

    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Yitzhak Khabinsky 25,026 Reputation points
    2022-01-21T17:10:19.877+00:00

    Hi @Don75 ,

    It is possible to use SQL Server Extended Events.
    You would need to capture a couple of events:

    • rpc_completed
    • sql_batch_completed

    And select in the Global Fields (Actions), event independed fields:

    • sql_text
    0 comments
  2. Erland Sommarskog 101.4K Reputation points MVP
    2022-01-21T22:48:35.463+00:00

    You can also do this with SQL Trace. You can use Profiler to set up the trace. You would include the events RPC:Starting and SQL:BatchStarting. (I think the Starting events are better for auditing purposes). Once you have started the trace, stop it immediately and use File->Export to script the trace definition. You will need to edit the file to set the file name, and to set the max size and the number of rollover files. Do absolutely not run the Trace from Profiler!

    The advantage with Extended Events is that you can define that the event session should start when the server starts, which is a good for auditing. With Trace, you would need to create a startup procedure for the task. The overhead is also somewhat lower with extended events for this case.

    The advantage with Trace is that the output is a lot easier to digest, as you get it in tabular format. XEvents gives you XML which takes a lot of human and computer power to process.

    0 comments
  3. Don75 81 Reputation points
    2022-01-26T15:40:10.873+00:00

    Thanks for all the replies. Much appreciated. Was able to resolve the issue.

    0 comments
  4. vijay sr 0 Reputation points
    2023-07-27T09:38:33.7033333+00:00

    how to ms sql log in windows

    0 comments