Capturing session level options settings on all sessions in SQL 2012 using XEvents


You could use XEvents to capture options set at all existing sessions & any future new sessions. Here is sample session definition. (I have tested it on SQL 2012 SP1). Please replace the file path in the following script to a valid file path on your machine where SQL Server service account has read, write privileges to create and write to XEvent file.

(Related forum thread: http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/feede34e-401c-44a6-8dcd-6a096ec84c38 )

XEventSession_SetOptions

T-SQL

———

CREATE EVENT SESSION [set_options] ON SERVER 
ADD EVENT sqlserver.existing_connection(SET collect_database_name=(1),collect_options_text=(1)) 
ADD TARGET package0.event_file(SET filename=N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\set_options.xel')
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
ALTER EVENT SESSION [set_options] ON SERVER STATE=START
Advertisements

Developer @ SQL Server Team, Microsoft

Posted in SQL Server, XEvents

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: