From time to time we are getting SQL Server error:
Error: 8623, Severity: 16, State: 1 The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.
To be able catch what is causing this error I have created an extended event (XE) session on XE event error_reported. The XE session seems to be working as other errors are logged but not 8623.
My XE session is created like this:
CREATE EVENT SESSION [ErrorReported] ON SERVER ADD EVENT sqlserver.error_reported( ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.sql_text,sqlserver.tsql_stack,sqlserver.username) WHERE ([severity]> 15)) ADD TARGET package0.event_file(SET filename=N'C:\trace\ErrorReported.xel',max_file_size=(250),max_rollover_files=(4)) 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=ON,STARTUP_STATE=OFF) GO
Anyone that help me to get XE to log also error 5623? Any help hugely appreciated.
We are currently running Microsoft SQL Server 2016 (SP2-CU10).