0

I need a way to identify which connection is causing error 17886. I've researched and there are solutions to fix the error, but only if you know which connection is causing the issue.

I want to log errors so I don't have to sit and wait for it to happen - if it does again. Is there a way to use extended events to track: Error #17886, connection, time it happened, etc.

Any ideas?

1 Answer 1

5

You can do this with an Extended Event Session. EG

CREATE EVENT SESSION [TrackError_17886] ON SERVER 
ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.username)
    WHERE ([error_number]=(17886)))
ADD TARGET package0.event_file(SET filename=N'TrackError_17886')
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=ON)
go

Will record the errors in files in your "log" folder.

6
  • Couple of questions if you don't mind so I learn what/why/how ... The MAX_MEMORY - does that keep it from taking up too much space? How do I remove after resolved - or would it matter? When you say my "log" folder, do you mean the folder I setup for logs or the system logs?
    – user80151
    Feb 10, 2021 at 20:59
  • MAX_MEMORY is documented here learn.microsoft.com/en-us/sql/t-sql/statements/… and it's the memory buffer that SQL Server fills up before writing to disk. You remove it by DROPing the Event Session in TSQL or SSMS. And that goes to the instance log folder, where the ERRORLOG is. Feb 10, 2021 at 21:18
  • 1
    Thanks, you're a gentleman and a scholar. I do appreciate the help!
    – user80151
    Feb 10, 2021 at 22:25
  • David - The error happened again and I can't find the logs anywhere. It is in the error log, but with the same info as before. Was it supposed to write to a file called "TrackError_17886" or was the set filename just to name the extended event? I double-clicked on it in SSMS but it's showing all zeroes for connectionID. Does that mean it didn't work? Sorry to ask here, I'm not sure if this is the proper way ...
    – user80151
    Feb 18, 2021 at 20:52
  • Is the Extended Event session running? Feb 18, 2021 at 22:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.