Wednesday, March 28, 2012

Replacement for sysprocesses dbid?

In SQLServer 2005, how can you select just the processes that are
connected to a specific database? In 2000, I could do it with this
query:
select *
from sysprocesses
where dbid = 5
BOL recommends using any of the dynamic management views
sys.dm_exec_connections, sys.dm_exec_sessions, or sys.dm_exec_requests
as a replacement for sysprocesses, but none of these has a database or
dbid column that I can find. Am I overlooking something? How do you
do this? Thanks.select * from sys.dm_exec_requests
where database_id = 5
to just see who is using a database, who might not be running any queries,
you can use sys.dm_tran_locks
select request_session_id from sys.dm_tran_locks
where resource_type = 'DATABASE'
and resource_database_id = 5
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
<stavros@.mailinator.com> wrote in message
news:1161904818.742946.108590@.m7g2000cwm.googlegroups.com...
> In SQLServer 2005, how can you select just the processes that are
> connected to a specific database? In 2000, I could do it with this
> query:
> select *
> from sysprocesses
> where dbid = 5
> BOL recommends using any of the dynamic management views
> sys.dm_exec_connections, sys.dm_exec_sessions, or sys.dm_exec_requests
> as a replacement for sysprocesses, but none of these has a database or
> dbid column that I can find. Am I overlooking something? How do you
> do this? Thanks.
>

No comments:

Post a Comment