I did a dangerous thing, and I want to make sure that YOU DO NOT do the same.

I was creating a couple of extended events sessions and was playing around with some actions. I ended up with the following code where I was after a guy called Shane:

CREATE EVENT SESSION GetShane ON SERVER 

ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,
    sqlserver.client_hostname, sqlserver.database_id,sqlserver.is_system,sqlserver.username,package0.debug_break,sqlserver.sql_text)
    WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[username],N'GetShane')
AND ( [sqlserver].[is_system] = ( 0 ))))

ADD TARGET package0.event_file(SET filename=N'c:\temp\GetShane')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF)
GO

ALTER EVENT SESSION GetShane
ON SERVER
STATE = START

With the above code now it is question time. Do you know what is so wrong with the above? Can you see it? Well soon as Shane connected to the SQL Server and issued some code this happened. (This is not Shane’s fault).

An error occurred while executing batch. Error message is: ExecuteReader requires an open and available Connection. The connection’s current state is closed.

 Below shows SQL Server Configuration Manager.

The issue here was that I accidentally/stupidly used package0.debug_break action which is invoked synchronously on the thread that fires an event therefore can have an impact to performance and in this case shutting down SQL Server to create debug breakpoint

Yes it’s probably quite obvious that it is dangerous BUT I wanted to share with you what ACTUALLY happens…so stay away from that one there is no real need to use it unless the Microsoft support people tell you otherwise because they know what they are doing unlike me!