发现数本地数据库连接数量很大，如何查看数据库连接是哪个用户在用的？

[txjstar]

我在分离数据库的地方看到使用本地数据库的连接数量很大，不知道可不可以从系统表中查看到这些连接是谁在用的？
分不多了，哪位知道，请告诉我，非常感谢！

[快乐_石头]

--SQl當前連接數

select count(spid) as counts, 
status, 
hostname, 
dbname = case 
when dbid = 0 then null 
when dbid <> 0 then db_name(dbid) 
end 
from master.dbo.sysprocesses 
where spid >=0 and spid <= 32767 and status='runnable' and db_name(dbid)='dbname' 
group by status,hostname, 

case 
when dbid = 0 then null 
when dbid <> 0 then db_name(dbid) 
end 

[快乐_石头]

SQl當前連接數2008-05-14 21:49select 连接数=count(distinct hostname) 
from master..sysprocesses 
where hostname<>'' and dbid=db_id('要查询的库名')

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_getlinkinfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) 
drop procedure [dbo].[p_getlinkinfo] 
GO 

/*--获取连接SQL服务器的信息 

所有连接本机的:操作的数据库名,计算机名,用户名,网卡物理地址,IP地址,程序名 
--邹建 2003.11--*/ 

/*--调用示例 
--显示所有本机的连接信息 
exec p_getlinkinfo 

--显示所有本机的连接信息,包含ip地址 
exec p_getlinkinfo @includeip=1 

--显示连接指定数据库的信息 
exec p_getlinkinfo '客户资料' 
--*/ 
create proc p_getlinkinfo 
@dbname sysname=null, --要查询的数据库名,默认查询所有数据库的连接信息 
@includeip bit=0 --是否显示IP地址,因为查询IP地址比较费时,所以增加此控制 
as 
declare @dbid int 
set @dbid=db_id(@dbname) 

create table #tb(id int identity(1,1),dbname sysname,hostname nchar(128),loginname nchar(128),net_address nchar(12),net_ip nvarchar(15),prog_name nchar(128)) 
insert into #tb(hostname,dbname,net_address,loginname,prog_name) 
select distinct hostname,db_name(dbid),net_address,loginame,program_name from master..sysprocesses 
where hostname<>'' and (@dbid is null or dbid=@dbid) 

if @includeip=0 goto lb_show --如果不显示IP地址,就直接显示 

declare @sql varchar(500),@hostname nchar(128),@id int 
create table #ip(hostname nchar(128),a varchar(200)) 
declare tb cursor local for select distinct hostname from #tb 
open tb 
fetch next from tb into @hostname 
while @@fetch_status=0 
begin 
set @sql='ping '+@hostname+' -a -n 1 -l 1' 
insert #ip(a) exec master..xp_cmdshell @sql 
update #ip set hostname=@hostname where hostname is null 
fetch next from tb into @hostname 
end 

update #tb set net_ip=left(a,patindex('%:%',a)-1) 
from #tb a inner join ( 
select hostname,a=substring(a,patindex('Ping statistics for %:%',a)+20,20) from #ip 
where a like 'Ping statistics for %:%') b on a.hostname=b.hostname 

lb_show: 
select id,数据库名=dbname,客户机名=hostname,用户名=loginname 
,网卡物理地址=net_address,IP地址=net_ip,应用程序名称=prog_name from #tb 

go  

SQl當前連接數

select count(spid) as counts, 
status, 
hostname, 
dbname = case 
when dbid = 0 then null 
when dbid <> 0 then db_name(dbid) 
end 
from master.dbo.sysprocesses 
where spid >=0 and spid <= 32767 and status='runnable' and db_name(dbid)='dbname' 
group by status,hostname, 

case 
when dbid = 0 then null 
when dbid <> 0 then db_name(dbid) 
end 


当网友问到我这个问题时,我也还以为很简单,以为SQL Server应该提供了对应的系统变量什么的.但是到目前为止,我还没有得到一个比较好的解决方案.可能很简单,,只不过我不知道罢了.希望如此..

下面我说说相关的知识吧.希望大家可以给出一个比较好的方法.

这里有几个与之相关的概念.

SQL Server提供了一些函数返回连接值(这里可不是当前连接数哟!),个人觉得,很容易产生误解.

系统变量

@@CONNECTIONS 返回自上次启动 Microsoft® SQL Server™ 以来连接或试图连接的次数。

@@MAX_CONNECTIONS 返回 Microsoft® SQL Server™ 上允许的同时用户连接的最大数。返回的数不必为当前配置的数值。


系统存储过程

SP_WHO

提供关于当前 Microsoft® SQL Server™ 用户和进程的信息。可以筛选返回的信息，以便只返回那些不是空闲的进程。

列出所有活动的用户:SP_WHO ‘active’

列出某个特定用户的信息:SP_WHO ‘sa’


系统表

Sysprocesses

sysprocesses 表中保存关于运行在 Microsoft® SQL Server™ 上的进程的信息。这些进程可以是客户端进程或系统进程。sysprocesses 只存储在 master 数据库中。

Sysperfinfo

包括一个 Microsoft® SQL Server™ 表示法的内部性能计数器，可通过 Windows NT 性能监视器显示.


有人提议说为了获取SQL Server的当前连接数:使用如下SQL:

SELECT COUNT(*) AS CONNECTIONS FROM master..sysprocesses


个人认为不对,看看.sysprocesses的login_time列就可看出.

另外一个方面是进程不能和连接相提并论,他们是一对一的关系吗,也就是说一个进程就是一个连接?一个连接应该有多个进程的,所以连接和进程之间的关系应该是1:n的.


因为sysprocesses列出的进程包含了系统进程和用户进程,为了得到用户连接,可以使用如下SQL:

SELECT cntr_value AS User_Connections FROM master..sysperfinfo as p
WHERE p.object_name = 'SQLServer:General Statistics' And p.counter_name = 'User Connections'

个人还是认为不对,因为它是一个计数器,可能会累加的.

还有一种方案是利用如下SQL:

select connectnum=count(distinct net_address)-1 from master..sysprocesses

理由是net_address是访问者机器的网卡值,这个总该是唯一的吧.但是看起来得到的是所有时间内的连接数.

希望大家可以给出自己的解决方案.这个问题解决了,相信会有很大的用途.


sp_who： 
sp_who 
提供关于当前 Microsoft® SQL Server™ 用户和进程的信息。可以筛选返回的信息，以便只返回那些不是空闲的进程。 

语法 
sp_who [[@login_name =] 'login'] 

参数 
[@login_name =] 'login' 

是 SQL Server 上的用户登录名。login 的数据类型为 sysname，默认值是 NULL。如果没有指定名称，过程将报告全部活动的 SQL Server 用户。login 也可以是特定进程标识号码 (SPID)。若要返回有关活动进程的信息，请指定 ACTIVE。ACTIVE 从报告中排除等待用户下一个命令的进程。 

返回代码值 
0（成功）或 1（失败） 

结果集 
sp_who 返回包含以下信息的结果集。 

列 数据类型 描述 
spid smallint 系统进程 ID。 
ecid smallint 与指定 SPID 相关联的给定线程的执行上下文 ID。 
ECID = {0, 1, 2, 3, ...n}，其中 0 始终表示主或父线程，并且 {1, 2, 3, ...n} 表示子线程。 

status nchar(30) 进程状态。 
loginame nchar(128) 与特定进程相关联的登录名。 
hostname nchar(128) 每个进程的主机或计算机名。 
blk char(5) 如果存在阻塞进程，则是该阻塞进程的系统进程 ID。否则该列为零。 
当与给定的 spid 相关联的事务受到孤立分布式事务的阻塞时，该列将对阻塞孤立事务返回 '-2'。 

dbname nchar(128) 进程使用的数据库。 
cmd nchar(16) 为此进程执行的 SQL Server 命令（Transact-SQL 语句、SQL Server 内部引擎处理等）。 


sp_who 结果集将根据 spid 值按升序排序。如果是并行处理，则会为特定的 spid 创建子线程。主线程表示为 spid =xxx，并且 ecid =0。其余的子线程同样也是 spid = xxx，但 ecid > 0。这样，将会为该 spid 编号返回多行 — 它们在整个列表中放置该 spid 的位置组合在一起。子线程将随机排列，但父线程除外 (ecid = 0)；在该 spid 中，父线程被首先列出。 

注释 
阻塞进程（可能含有排它锁）是控制其它进程所需要的资源的进程。 

在 SQL Server 2000 中，所有孤立 DTC 事务的 SPID 都被赋予值 '-2'。孤立 DTC 事务就是不与任何 SPID 相关联的分布式事务。这样，当某一孤立事务阻塞其它进程时，可由其特殊的 '-2' SPID 值来识别该孤立分布式事务。有关更多信息，请参见 KILL。 

SQL Server 2000 保留从 1 到 50 的 SPID 值以便内部使用，而 51 或更大的 SPID 值则代表用户会话。 

权限 
执行权限默认授予 public 角色。 

示例 
A. 列出全部当前进程 
此示例使用没有参数的 sp_who 报告所有当前用户。 

USE master 
EXEC sp_who 

下面是结果集： 

spid ecid status loginame hostname blk dbname cmd 
---- ---- ------ ------------ -------- --- ------ ----- 
1 0 background sa 0 pubs LAZY WRITER 
2 0 sleeping sa 0 pubs LOG WRITER 
3 0 background sa 0 master SIGNAL HANDLER 
4 0 background sa 0 pubs RA MANAGER 
5 0 background sa 0 master TASK MANAGER 
6 0 sleeping sa 0 pubs CHECKPOINT SLEEP 
7 0 background sa 0 master TASK MANAGER 
8 0 background sa 0 master TASK MANAGER 
9 0 background sa 0 master TASK MANAGER 
10 0 background sa 0 master TASK MANAGER 
11 0 background sa 0 master TASK MANAGER 
51 0 runnable DOMAIN\loginX serverX 0 Nwind BACKUP DATABASE 
51 2 runnable DOMAIN\loginX serverX 0 Nwind BACKUP DATABASE 
51 1 runnable DOMAIN\loginX serverX 0 Nwind BACKUP DATABASE 
52 0 sleeping DOMAIN\loginX serverX 0 master AWAITING COMMAND 
53 0 runnable DOMAIN\loginX serverX 0 pubs SELECT 
(16 row(s) affected) 

B. 列出特定用户的进程 
此示例显示如何通过登录名查看有关单个当前用户的信息。 

USE master 
EXEC sp_who 'janetl' 

C. 显示所有活动进程 
USE master 
EXEC sp_who 'active' 

D. 通过进程 ID 显示特定进程 
USE master 
EXEC sp_who '10' --specifies the process_id  
 

[让你望见影子的墙]

sp_who

就够用了

[txjstar]

谢啦，问题已经解决

[hyde100]

--功能概述:查看特定数据库连接信息

DECLARE @dbName nvarchar(50)--数据库名称
SET @dbName = 'stock'

SELECT * 
FROM Master.dbo.SYSPROCESSES
WHERE [DBID] IN 
(
	SELECT 
		DBID
	FROM 
		Master.dbo.SYSDATABASES
	WHERE [NAME] = @dbName
)

[hyrongg]

sp_who