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')
CREATE EVENT SESSION [ApplicationErrors] ON SERVER
ADD EVENT sqlserver.error_reported(
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)
--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!