| Lesson 2 || Connections |
| Objective || Monitor SQL Server connections. |
Monitor SQL Server Connections
When users access SQL Server, they make a connection
to SQL Server.
All connections exist independently of one another, even if the same application makes multiple connections. SQL Server allows you to monitor the activity of each connection through SQL and SQL-EM.
Using SQL to monitor connections
The simplest way to monitor how users and processes are connected with SQL is to use the system stored procedure 
This procedure will retrieve information about all active connections. The syntax of
is as follows:
sp_who [[@login_name =] "login|spid|ACTIVE"]
While SQL key words are always case independent, stored procedure names' case sensitivity depends on the sort order installed.
You should always type stored procedure names in lower case.
The one parameter for
@login_name, allows you to limit the results of
sp_who to a specific login, or spid,
or to all active processes.
sp_who produces the following output: (Note that this output has been reformatted to fit on your screen).
A note about status
output, you may see different types of status. The most common ones are:
- background: Identifies this as a SQL Server task.
- runable: The task would run if it could get access to the CPU.
- running: The process is running, sleeping.
- waiting: The process is waiting.
In the next lesson, another method for monitoring connections will be discussed.
A person or application that has logged onto SQL Server.
 System stored procedure:
A procedure stored in the master database which begins with sp_ that is used to perform system functions.