DMV that replaces sp_who in sql 2005

Question

DMV that replaces sp_who in sql 2005

beejug1983

SSCertifiable

Points: 6491
More actions
December 7, 2011 at 4:41 pm

#247344

Hi,
Can someone remind me what's the DMV that replaces sp_who in sql 2005? Supposed to be able to pass the database name to see spids on specific database.
Thanks in advance

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

There is not a DMV that replaces sp_who or sp_who2 in its entirety.

If you are looking for a query that is useful you could try whoisactive by Adam Machanic, or take a look at this article[/url] (to see how some of it is put together).

A simple query to a DMV that shows some info is:

SELECT * FROM sys.dm_exec_requests

WHERE database_id = DB_ID(DB_NAME())

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 2

sp_who is still available in SS2K8 R2. I don't find any note on BOL that indicates its deprecation in Denali or later.

sp_who (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ms174313(v=SQL.105).aspx

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Not one, but there are three that when combined mostly replace it (there is still some bits missing)

sys.dm_exec_connections

sys.dm_exec_sessions

sys.dm_exec_requests

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

beejug1983 SSCertifiable Points: 6491 More actions · Answer 4

beejug1983

SSCertifiable

Points: 6491

December 8, 2011 at 6:40 am

#1418653

Thanks all for replies.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

You're welcome.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Dev (12/8/2011)
sp_who is still available in SS2K8 R2. I don't find any note on BOL that indicates its deprecation in Denali or later.
sp_who (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms174313(v=SQL.105).aspx

Correct. Part of the problem with who2 is that it relies on sysprocesses (which is scheduled to be deprecated). sysprocesses is supposed to be replaced by the three DMVs that Gail listed but they do not fully replace it yet. Some of the info that people rely on from sysprocesses just can't be found in the new DMVs.

I imagine once MS corrects those DMVs and removes sysprocesses, then they will modify who2 to use the new DMVs. But they probably will not remove it.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events