Xjyuk

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:

• https://blog.devart.com/capturing-sql-server-trace-data.html

Here's an alternative using deprecated features:

• https://docs.microsoft.com/en-us/sql/tools/sql-server-profiler/sql-server-profiler?view=sql-server-2017

Is there a non-deprecated, native solution?

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:XETrackQueries.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.

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.

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