Click here to Skip to main content
Click here to Skip to main content

Finding 'who is' using SQL Server

, 4 Nov 2003
Rate this:
Please Sign up or sign in to vote.
An article showing how to display the sp_who info and sort on a column.

Introduction

There are times when you will need to run sp_who on your SQL Server to figure out who is on and what are they doing. The stored procedure is fine for this, but it is kind of awkward to see the output. There are several wide columns and unless you rewrite the proc, you can’t tailor the output. After looking into what queries were actually being done, I decided that it could be done in a program and the results listed in a grid. I wanted to be able to sort on any column, and have it remember which column I sorted last. Since I usually run sp_who to kill some process, I also wanted to add a kill command.

This project accomplishes all this and also shows how to get info from a database, sort on columns, save info in a .config file, and encrypt passwords. For the encryption, I am indebted to another Code Project user, Syed Adnan Ahmed for his article Encrypt Password Field in SQL Server, Registry Information & Query String. Although the article is written in VB.NET, it’s amazing how easy it is to convert it to C#.

The main data structure that is used to hold the sp_who data is WhoInfo.

public class WhoInfo
{
    int spid;
    string status;
    string loginame;
    string hostname;
    string blk;
    string dbname;
    string cmd;
    long physical_io;
    int memusage;
}

This data is to be loaded by running a query on the database.

SELECT spid, status, RTRIM(loginame) AS loginame, hostname, 
  CONVERT(char(5), blocked) AS blk, 
  DB_NAME(dbid) AS dbname, cmd, physical_io, memusage 
  FROM master.dbo.sysprocesses where ecid = 0

This is almost the identical query that the sp_who command executes, with the exception that I only return one thread per process. It would not be too hard to add another dialog to display a list of threads if needed. I get the info into a DataReader and then loop over the rows, adding to the WhoInfo structure and then to the ListView. I chose ListView because I wanted to sort on the columns. The catch with sorting is that the sort will work using an alphabetic collation. This is fine until the column contains numbers. In the case of numeric data, the sort needs a little help. I furnish this in the form of the ColumnSorter class. This class is passed to the ListView’s ListViewItemSorter method. Then any sorting done accesses the columns Compare method. We only need the Compare method, which sorts according to the data type. If you change the column order, this will need to change also.

Connect and Kill your users

There are two dialogs in this application. One collects the login info for the database connection. The other displays another list of the locks a process has. The password for the server is stored encrypted, so there is no problem installing this where you only want a user to see who is on, and possibly kill them, (their process that is). The Kill command is accessed by a right click menu, and the locks can be displayed by double clicking on a list entry.

Configuration files

The config file is another interesting feature. I use a DataSet to manipulate the data by using a ReadXML method to get the data. If a file does not exist, I create a template one. This file stores the database server name and password. It also keeps track of the last column that was sorted. This gives a simple example of using a StreamWriter.

Conclusion

I hope the various parts of this application are useful to others to give them snippets of code. The true bonus is that it is a very useful application, at least to SQL Server administrators.

History

Version 1.0 - Initial revision

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Steve Brailsford

United States United States
No Biography provided

Comments and Discussions

 
GeneralStrange Pinmemberwebooth12-Nov-03 8:12 
GeneralRe: Strange Pinmember#teve12-Nov-03 8:51 
GeneralRe: Strange Pinmemberwebooth12-Nov-03 9:45 
GeneralRe: Strange PinmemberSenkwe Chanda17-Nov-03 22:57 
GeneralRe: Strange Pinmemberwebooth18-Nov-03 2:05 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.141015.1 | Last Updated 5 Nov 2003
Article Copyright 2003 by Steve Brailsford
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid