Capture SQL Server queries without third-party tooling and without using deprecated features?
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.
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.