How do I mimic sp_who2 with SQL Server 2005s new dynamic management views?
sp_who2 is a useful tool in learning about the current activity in SQL Server. However, there are some flaws in the design, including using (NOLOCK) on virtual tables (which is harmless but useless), ignoring the @loginame parameter when it is neither ‘active’ nor NULL, and relying on soon-to-be-deprecated objects like sysprocesses and syslockinfo. Someone at Microsoft tasked the MVPs with replicating sp_who2 in SQL Server 2005, using only the dynamic management views — and, in particular, staying away from sysprocesses. I managed to whip something up quickly, with a few caveats. I did not bother replicating the @loginame functionality in this version, because Microsoft’s version doesn’t work either. Also, there are a few pieces of information that are simply not available in the new DMVs, for example commands with values ‘AWAITING COMMAND’ and ‘UNKNOWN TOKEN’ are nowhere to be found. Finally, some metrics for tasks like ‘LOG WRITER’ and ‘CHECKPOINT’ seem to be missing, or I just don’t
