Wednesday, June 30, 2010

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)

1 comment:

Jacob Sebastian said...

Very interesting collection of tips.

I was trying to contact you but none of the links on the web page gave me a contact option. can you connect with me on jacob at beyondrelational dot com?

(and of course delete the second part of this comment :-)