SQL Server: Showing a list of open database connections

Today I wanted to drop a specific database, but their was one account still connected. I had no idea who was still using the database, so I needed a way to get the list of open database connections.

There are 2 commands that helped me out here:

• sp_who
• sp_who2

The sp_who internal procedure allows users to view current activity on the database. This command provides a view into several system tables (e.g., syslocks, sysprocesses, etc.). The sp_who command returns the following information:

• Spid—The system process ID.
• status—The status of the process (e.g., RUNNABLE, SLEEPING).
• loginame—Login name of the user.
• hostname—Machine name of the user.
• blk—If the process is getting blocked, this value is the SPID of the blocking process.
• dbname—Name of database the process is using.
• Cmd—The command currently being executed (e.g., SELECT, INSERT)

The sp_who2 internal procedure provides the above information, but also provides the following additional information:

• CPUTime—Total CPU time the process has taken.
• DiskIO—Total amount of disk reads for the process.
• LastBatch—Last time a client called a procedure or executed a query.
• ProgramName—Application that has initiated the connection (e.g., Visual Basic, MS SQL Query Analyzer)