System Views, or "Who's connected to my database?"

  • Article

The other day I was trying to drop a database, but I was getting an error because it was in use. Given that I was the only user of the database, I was a bit puzzled... I had a number of programs running, and I wasn't sure which one might be holding on to a collection.

Enter System Views, and more specifically, one particular dynamic management one: sys.dm_exec_connections. This little beauty has a wealth of information (Joe Sack mentions a few interesting uses), but you can get oh-so-much goodness with a few simple joins. Eventually, this is the query I ended up using (notice that some lines are commented out, you can uncomment them to get some additional details that might also come in handy).

select
dec.connect_time,
-- dec.net_transport, dec.protocol_type,
dec.auth_scheme,
-- dec.num_reads, dec.num_writes, dec.last_read, dec.last_write,
-- dec.client_net_address, dec.client_tcp_port,
e.name,
dest.text,
-- des.session_id,
des.host_name, des.program_name, des.host_process_id, des.login_name, des.status
from sys.dm_exec_connections as dec
left join sys.endpoints as e on dec.endpoint_id = e.endpoint_id
cross apply sys.dm_exec_sql_text(dec.most_recent_sql_handle) as dest
left join sys.dm_exec_sessions as des on dec.session_id = des.session_id

So, what are some of the interesting things you get with this?

  • connect_time: when did the connection take place?
  • auth_scheme: what authorization was used? (eg: NTLM)
  • name: this is the endpoint name, which condenses a bunch of human-readable information (eg, 'TSQL Named Pipes')
  • text: this is the text for the last query executed (eg, 'SELECT * FROM somewhere'). Notice the use of sys.dm_exec_sql_text, a table-valued function.
  • host_name: machine name.
  • program_name: name of the program running the connection; you may need to tweak your connection strings to get the value you want from this.
  • host_process_id: ID of the process - awesome to really track down which program is holding on to the connection.
  • login_name: name of the user logged in, in my case with NTLM, it's in domain\user format.
  • status: whether something is running, or whether the connection is just waiting for something (a few additional states are also available)

If you have a few minutes, it's very much worth it to look at the system views and what they have to offer. They have great potential for helping you monitor work and diagnose problems in an automated manner.

 

This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at https://www.microsoft.com/info/cpyright.htm.