按数据库分组sys.dm_exec_connections（它不像sys.sysprocesses） [英] Grouping sys.dm_exec_connections by database (it's not quite like sys.sysprocesses)

问题描述

继续我的最后一个问题：

我写了一些代码来升级SQL Server数据库。在升级数据库之前，我计划使用以下语句限制对数据库的访问：

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.

在SQL 2000中，您可以使用 sys.sysprocesses 表以查看适用于数据库的所有连接。这包括没有活动请求的连接（例如当您打开查询分析器窗口并选择数据库时）。

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).

但是，使用：

• sys.dm_exec_connections


• sys.dm_exec_sessions ;和


• sys.dm_exec_requests

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

我不能找出一种方法来实现相同的结果。看起来这些视图仅通过请求来连接到数据库。有没有办法模仿 sys.sysprocesses 的行为？我不想在SQL Server 2005/2008数据库中使用此表。

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.

推荐答案

您的其他问题。

很抱歉，我发现您仍然需要使用sysprocesses

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

它被记录为 Microsoft Connect 144515 中的错误，需要修复，我在这里找到

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

就个人而言，我仍然使用sysprocesses，因为我很舒服，但是lazy和luddite可能是...

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

这篇关于按数据库分组sys.dm_exec_connections（它不像sys.sysprocesses）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！