Finding 'who is' using SQL Server

• Download source - 16.5 Kb

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