IF NOT EXISTS extended eventsession not working, kinda stuck

  • I've tested on Developer Edition    SQL 2014   through 2016 Enterprise, so it's the code, not the server.

    I'm trying to simply wrap my code with an IF NOT EXISTS() CREATE EVENT SESSION... logic.i do that same thing for all my other logic, procs, tables etc.

    if i run the code below twice in a row, the second time reports a classic error like we've all seen previously:

    Msg 25631, Level 16, State 1, Line 11
    The event session, "ApplicationErrors", already exists. Choose a unique name for the event session.

    so fine, the error must be in my query right? but SELECT * FROM sys.[dm_xe_sessions] does not show my newly created session, but SSMS Object Explorer shows me it already exists.
    I've used that dmv lots of times previously, as I use it to script out extended events, and create views over the top of it.
    whether it's caffeine deprivation or something else, i cannot find the session I literally just created via a TSQL script. someone show me what i missed!

    here's my code:
    IF NOT EXISTS(SELECT * FROM sys.[dm_xe_sessions] AS [dxs] WHERE [dxs].[name] = 'ApplicationErrors')
    BEGIN
       CREATE EVENT SESSION [ApplicationErrors] ON SERVER
       ADD EVENT sqlserver.error_reported(
        ACTION(package0.event_sequence,
           package0.last_error,
           sqlserver.client_app_name,
           sqlserver.client_hostname,
           sqlserver.database_id,
           sqlserver.database_name,
           sqlserver.nt_username,
           sqlserver.plan_handle,
           sqlserver.query_hash,
           sqlserver.query_plan_hash,
           sqlserver.session_nt_username,
           sqlserver.sql_text,
           sqlserver.username)
          WHERE ([package0].[greater_than_equal_int64]([severity],(14))
          --AND [sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[client_hostname],N'SolarWinds')
            )
          )
       ADD TARGET package0.event_file(SET filename=N'ApplicationErrors.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)
    END

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • My first thought was that sys.dm_xe_sessions is for active sessions where as sys.server_event_sessions would be for any session defined on the server. I've been using the later when creating event sessions. Or maybe I'm missing something and it's me who needs more caffeine.

    Sue

  • sys.dm_xe_sessions only shows active sessions, I believe.

    Try sys.server_event_sessions instead.

    Cheers!

    EDIT: Got distracted while typing it up, and Sue beat me to it 🙂

  • Jacob Wilkins - Thursday, June 1, 2017 8:20 AM

    sys.dm_xe_sessions only shows active sessions, I believe.

    Try sys.server_event_sessions instead.

    Cheers!

    EDIT: Got distracted while typing it up, and Sue beat me to it 🙂

    Then maybe you and I are good on the caffeine today. Or we both are suffering shortages.

    Sue

  • yes, that was it, thank you Jacob and Sue!
    i was locked into search dmvs with *xe* in them.
    Awesome, and thank you again!

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply