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.statusfrom 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?
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 http://www.microsoft.com/info/cpyright.htm.
PingBack from http://www.artofbam.com/wordpress/?p=5572