Scribd Logo
Upload

Welcome to Scribd!

  • 12 views

    Alation Sqlserver Query Log SQL

    Uploaded by

    Rodrigo Valenzuela
    query log in alation

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Alation Sqlserver Query Log SQL

    Uploaded by

    Rodrigo Valenzuela
    12 views4 pages
    query log in alation

    Original Title

    alation_sqlserver_query_log_sql

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    query log in alation

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    12 views4 pages

    Alation Sqlserver Query Log SQL

    Uploaded by

    Rodrigo Valenzuela
    query log in alation

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    You are on page 1of 4

    INSTRUCTIONS

    SQL Server
    Query History Configuration
    Query Log Ingestion is done using Extended Events

    STEPS

    1 Grant Alation Service Account Why is this needed?


    VIEW SERVER STATE permission
    Alation must have access to run
    sys.fn_xe_file_target_read_file
    command to read the logged XEL files that
    extended events creates.
    VIEW SERVER STATE is required to use
    that command.

    2 Grant Alation Service Account permission to run Why is this needed?


    the stored procedure xp_dirtree
    Alation needs to read the list of logged XEL
    files that it is going to ingest.
    This stored procedure lists files in a
    directory.

    3 Create and turn on an extended events session


    that logs queries. See Section A for the
    SQL code for SQL Server versions greater than
    2008 and Section B for SQL Server 2008. You will
    have to change the file path to log the files and
    may optionally change some of the other param-
    eters like the buffer size.

    4 When you first turn on the session monitor it for a


    few hours to see that it is working and the volume
    of log files. If the volume is growing such that log
    files will be rolled over within 3 days then turn off
    logging and contact Alation support who will help
    with the process of creating a custom extended
    events session to filter out some of those queries
    that are not useful.

    5 Have the Alation Admin for your instance config-


    ure the settings from the Alation UI to point to the
    files that are being logged.
    SECTION A

    SQL Server 2009+ Events Creation

    ­­-- Query To Create Extended Events Session


    CREATE EVENT SESSION [alation_query_log] ON SERVER ADD EVENT sqlserver.
    sp_statement_completed(
    ACTION(package0.collect_system_time,package0.event_sequence,sqlos.task_
    time,sqlserver.cl ient_app_name,sqlserver.database_id,sqlserver.database_
    name,sqlserver.nt_username,sqlser ver.server_principal_name,sqlserver.
    server_principal_sid,sqlserver.session_id,sqlserver.sessio n_nt_user-
    name,sqlserver.transaction_id,sqlserver.username)
    WHERE ([statement] like ‘%ALTER %’ OR
    [statement] like ‘%CREATE %’ OR
    [statement] like ‘%DROP %’ OR
    [statement] like ‘%TRUNCATE %’ OR
    [statement] like ‘%MERGE %’ OR
    [statement] like ‘%FROM %’ OR
    [statement] like ‘%USE %’
    )),
    ADD EVENT sqlserver.sql_statement_completed(SET collect_statement=(1)

    ACTION(package0.collect_system_time,package0.event_sequence,sqlos.task_
    time,sqlserver.cl ient_app_name,sqlserver.database_id,sqlserver.data-
    base_name,sqlserver.nt_username,sqlser ver.server_instance_name,sqlserver.
    server_principal_name,sqlserver.session_id,sqlserver.sess ion_nt_user-
    name,sqlserver.transaction_id,sqlserver.username)
    ­­ -- Generic Alation filters, we only make use of certain types of
    -- statements
    ­­ -- Note SELECT is omitted because SELECT without FROM is not useful
    -- for our analysis
    WHERE ([statement] like ‘%ALTER %’ OR
    [statement] like ‘%CREATE %’ OR
    [statement] like ‘%DROP %’ OR
    [statement] like ‘%TRUNCATE %’ OR
    [statement] like ‘%MERGE %’ OR
    [statement] like ‘%FROM %’ OR
    [statement] like ‘%USE %’
    ))
    ADD TARGET package0.event_file(
    SET filename=N’C:\Users\All Users\Documents\alation_query_log.xel’, ­­
    -- CONFIGURE THIS ­­ OPTIONALLY CONFIGURE THIS, max file size in
    -- MB before rolling over
    -- Note Alation will read one file at a time so this is the size of
    -- file that may be­ read into memory while it is being fetched.
    max_file_size=(100), ­­ -- in MB
    max_rollover_files=(100)) ­­-- OPTIONALLY CONFIGURE THIS
    WITH (MAX_MEMORY=50 MB,
    -- OPTIONALLY CONFIGURE THIS, the total event buffer size
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, ­­
    -- If buffer is full events will be dropped instead of blocking
    -- the server
    MAX_DISPATCH_LATENCY=30 SECONDS,
    --­­ OPTIONALLY CONFIGURE THIS, max time before writing events to
    -- storage
    MAX_EVENT_SIZE=0 KB, ­
    -- Any events that are too large to fit in the buffer will be
    -- dropped
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=ON
    )
    GO
    -- Query to start the session (change START ­ > STOP to stop it)
    ALTER EVENT SESSION alation_query_log ON SERVER
    STATE = START
    go
    -- If you need to delete and recreate the session this is the syntax
    -- to drop it.
    DROP EVENT SESSION [alation_query_log] ON SERVER
    GO
    -- Check if the session is dropping events and see other data about
    -- the session
    SELECT * FROM sys.dm_xe_sessions;
    SECTION B

    SQL Server 2008 Events Creation

    ­­-- Query To Create Extended Events Session


    CREATE EVENT SESSION alation_query_log ON SERVER
    ADD EVENT sqlserver.sql_statement_completed( ACTION(package0.collect_sys-
    tem_time,sqlos.task_time,sqlserver.client_app_name,sqlserver.d atabase_
    id,sqlserver.nt_username,sqlserver.session_id,sqlserver.session_nt_user-
    name,sqlserv er.transaction_id,sqlserver.username, sqlserver.sql_text))
    ADD TARGET package0.asynchronous_file_target(
    SET filename=N’C:\Users\Public\Documents\alation_query_log.xel’,
    -- CONFIGURE THIS ­
    -- OPTIONALLY CONFIGURE THIS, max file size in MB before rolling over
    -- Note Alation will read one file at a time so this is the size
    -- file that may be read into memory while it is being fetched.
    max_file_size=(100), ­­
    -- in MB
    max_rollover_files=(100)) ­­ -- OPTIONALLY CONFIGURE THIS
    WITH (MAX_MEMORY=50 MB, ­­
    -- OPTIONALLY CONFIGURE THIS, the total event buffer size
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, ­­
    -- If buffer is full events will be dropped instead of blocking
    -- the server
    MAX_DISPATCH_LATENCY=30 SECONDS, ­­
    -- OPTIONALLY CONFIGURE THIS, max time before writing events to
    -- storage
    MAX_EVENT_SIZE=0 KB, ­­
    -- Any events that are too large to fit in the buffer will be
    -- dropped
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=ON
    )
    GO
    ­­
    -- Query to start the session (change START ­
    > STOP to stop it)
    ALTER EVENT SESSION alation_query_log ON SERVER
    STATE = START
    go
    -- If you need to delete and recreate the session this is the syntax to
    -- drop it.
    DROP EVENT SESSION [alation_query_log] ON SERVER
    GO
    -- Check if the session is dropping events and see other data about the
    ­­
    session SELECT * FROM sys.dm_xe_sessions;

    You might also like