Login Timeouts

I was recently approached at work about a company that was seeing tons of timeouts in SQL Server. I was asked how to troubleshoot this. There are lots of posts by people on this topic, but I found something I didn’t see anywhere else, let me share it with you.

Extended Events

You must have known I was going to bring up Extended Events. Surely. Well, I am. Now, if you search up “timeout” in the events, you find lock timeouts, execution plan timeouts (ooh), and stuff like that. It’s not related to the login timeout. So, look up “connection” or “log in”. You get a lot of information, but again, none of it is related to timeouts. In fact, the best info is in process_login_finish. It does include login errors, but, funny enough, a timeout is not a finished login process, so, yep. Nothing. However, go and read the article I linked above (or others on troubleshooting connection timeouts). There is one place where you can see failed connections, the ring buffer.

So, search for “ring_buffer” and you find, well, nothing useful. So what do you need to do? Enable the Debug channel.

Then, you’ll find that you have a whole bunch more events to look at. Yes, Debug events are subject to change without notice. Some Debug events are even dangerous to use. I strongly urge caution before you just start tossing these around. That said, check out this event:

That reads: connectivity_error_ring_buffer_recorded. In short, yes, you can follow the advice in the article above and query the ring buffer.

OR

You can set up an Extended Event session and capture the errors as they get written to the ring buffer. I can even do you one better. You can make sure that you only capture timeouts:

Right there at the bottom of the image: type = LoginTimers. That’s the one. Your session would then look something like this:

CREATE EVENT SESSION ConnectionTimeout
ON SERVER
    ADD EVENT sqlserver.connectivity_ring_buffer_recorded
    (ACTION
     (
         sqlserver.client_app_name,
         sqlserver.client_hostname,
         sqlserver.nt_username,
         sqlserver.username
     )
     WHERE (type = 'LoginTimers')
    )
    ADD TARGET package0.ring_buffer;

Conclusion

I can’t stress enough that this isn’t going to tell you everything you want to know about connection timeouts. However, it is going to tell you so much more than you know now, and, you get to capture the information on the fly. Here’s how I added this to Redgate SQL Monitor to assist the customer. One more place where Extended Events shines and gives you information you simply can’t get elsewhere.