June 1, 2017 at 7:42 am
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
June 1, 2017 at 8:17 am
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
June 1, 2017 at 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 🙂
June 1, 2017 at 8:47 am
Jacob Wilkins - Thursday, June 1, 2017 8:20 AMsys.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
June 1, 2017 at 9:17 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply