I Like Extended Events
You can do a lot of really cool stuff with them. But the documentation can leave a bit to the imagination. Or a lot.
For instance, figuring out something as simple as the unit being used for duration can even be dicey.
Enter the sql_exit_invoked Event
I saw this question on Database Administrators Stack Exchange the other day, and it caught my interest:
I’ve looked at the built-in system_health XE session a number of times, but have never really considered that particular event. So I decided to try and dig a little deeper.
Turns out this is a debug channel event. If information is sparse about Extended Events in general, the debug channel can be a real wasteland.
The description given in the XE session GUI in SSMS says:
Occurs when SQLExit() routine is invoked
Too bad we have know way of really knowing what
SQLExit() is or what it does. However, from the name it certainly seems to be related to shutting down SQL Server. And in fact, if we look at the event fields, there are only two:
There’s a nice empty description box next to each of them. Whatevs.
“Exit Code” is a pretty standard programming trope, where “0” is good, and anything else is some kind of error.
sys.dm_xe_map_values shows there are four values available for the “shutdown_option” field. So my mission was to see how to get each of those four values.
Shut It Down
(Did anyone get that 30 Rock reference? No? Okay)
First of all, I’ll set up an XE session as similar as possible to the one in the system_health session, but with just the
CREATE EVENT SESSION [sql_exit_invoked] ON SERVER ADD EVENT sqlserver.sql_exit_invoked( ACTION( package0.callstack, sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.client_pid, sqlserver.query_hash, sqlserver.session_id, sqlserver.session_nt_username) ) ADD TARGET package0.event_file(SET filename=N'sql_exit_invoked') WITH (STARTUP_STATE=ON)
And now, I’ll try a bunch of different ways of stopping SQL Server.
|What I did||shutdown_option Value||Reaction|
|Restart the SQL Server Service cleanly
Restart the machine cleanly
|ORDERLY_SHUTDOWN||That makes sense|
||FAST_SHUTDOWN||It could be faster though|
|Kill the sqlservr.exe process with TaskMgr||Too fast|
|Restart the machine not cleanly
(by holding the power button)
There is a 4th option for this field (called “SHUTDOWN_NOT_SET”). I heard from some guy at Microsoft that this is probably just some kind of initial value, and that you wouldn’t see it logged in practice.
How This Helps
While knowing what these shutdown_options mean won’t solve all your problems (I mean, your SQL Server instance is restarting), now you can at least have a direction to look in.
- Seeing ORDERLY_SHUTDOWN? Investigate the Windows Event Logs to see who is restarting the server, or what services are sending a
net stopcommand to the SQL Server service
- Seeing NICE_SHUTDOWN or FAST_SHUTDOWN? Good news: in the default XE, the hostname, app name, and session username are all logged
- Go track down that user or app and ask them “why are you doing this crazy thing?”
- Or better yet, just remove their sysadmin rights, since that’s what’s required to run
- Seeing SHUTDOWN_NOT_SET? Tell me how you did it!
Here’s a screenshot from my test where you can see that “jdarnell” (hey, that’s me) is the culprit, and he used SSMS to do it:
It can also be helpful to know the limitations of the tools you’re using. Now I know that the
sql_exit_invoked event won’t pick up situations where someone forcefully killed the SQL Server process, or hard-rebooted the machine it’s running on.