6

Is it possible to capture all queries sent to an MS SQL Server, without third-party tooling and without using deprecated features?

I'm looking for something similar to the "general query log" in MySQL.

Here's an example using a third-party tool:

Here's an alternative using deprecated features:

Is there a non-deprecated, native solution?

  • What version of SQL Server? – Taryn Jul 22 at 19:24
  • 2012 and later........ – Alex R Jul 22 at 19:25
  • 1
    Is Extended Events an option for you? – Erik Darling Jul 22 at 19:36
12

You could use Extended Events to capture this data. However, depending on how much traffic your server gets this could become a lot of data very quickly and could result in performance issues.

I would look at limiting the events that you track to something like sqlserver.rpc_completed or sqlserver.sql_statement_completed, these only capture what was completed. Erin Stellato wrote a great piece on useing XEvent Profiler to capture queries in SQL Server.

From SSMS, you would go to Management > Extended Events > Session and either use the New Session Wizard or New Session to start building your session to track data. The Microsoft Docs go into a lot of detail on how to set this up.

Here is an example of something I implemented recently to capture queries on a specific server, you can add filters to remove queries that you don't want to see - like some server names or application names that execute queries as well:

CREATE EVENT SESSION [Track Queries] ON SERVER 
ADD EVENT sqlserver.rpc_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.sql_text,sqlserver.username)
    WHERE (NOT [sqlserver].[like_i_sql_unicode_string]([sqlserver].[client_hostname],N'%name%') 
            AND [sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[client_hostname],N'name') 
            AND NOT [sqlserver].[like_i_sql_unicode_string]([sqlserver].[client_app_name],N'%name%') 
            AND [sqlserver].[server_principal_name]<>N'<username>')),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.sql_text,sqlserver.username)
    WHERE (NOT [sqlserver].[like_i_sql_unicode_string]([sqlserver].[client_hostname],N'%<name>%') 
            AND [sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[client_hostname],N'name') 
            AND NOT [sqlserver].[like_i_sql_unicode_string]([sqlserver].[client_app_name],N'%name%') 
            AND [sqlserver].[server_principal_name]<>N'<username>'))
ADD TARGET package0.event_file(SET filename=N'D:\XE\TrackQueries.xel',max_file_size=(5120))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

This captures the details of what has been executed and puts it into a file for easy querying and analysis.

7

Use SQL Server Extended Events to capture T-SQL statements as they execute.

SQL Server Management Studio 17+ includes an "XEvent Profiler" item in the Object Explorer for every connected SQL Server that is Version 2012 or higher. Right-Click the TSQL session, then Launch Session.

Be aware, capturing T-SQL statements across an entire server can negatively affect performance, so you'd likely only want to do that for a short period of time before stopping the session.

0

Extended events seems like the best bet from what i've seen:

https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/extended-events?view=sql-server-2017

Logging queries and other T-SQL

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.