Grouping sys.dm_exec_connections by database (it's not quite like sys.sysprocesses)

Question

Following on from my last question:

I've written some code to upgrade a SQL Server database. Before I upgrade the database, I plan to limit access to the database with the following statement:

ALTER DATABASE Test SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Before running this code, I'll give the user an opportunity to opt out. At the time of prompting the user, I thought it would be nice to show the list of active connections (continuously polled at a set interval); providing the user with a tool to identify applications/users they would like to boot off the server before proceeding.

In SQL 2000, you can use the sys.sysprocesses table to see all connections that apply to a database. This includes connections that have no active request (like when you open a Query Analyser window and select a database).

However, using:

• sys.dm_exec_connections
• sys.dm_exec_sessions; and
• sys.dm_exec_requests

I couldn't figure out a way to achieve the same outcome. It appears that these views only tie connections to a database through a request. Is there a way to mimic the behaviour of sys.sysprocesses? I'd prefer not to use this table for SQL Server 2005/2008 databases.

Answer 1

Er... I recommended these for your other question.

Sorry, but, I've found out that you still have to use sysprocesses

It's logged as a bug in Microsoft Connect 144515 to be fixed, I found it here

Personally, I still use sysprocesses because I'm comfortable with it, however lazy and luddite that may be...