Page 176 - DCAP508_DATABASE_ADMINISTRATION

Database Administration

Notes 12.7 Monitoring with SQL Server Enterprise Manager

Use SQL Server Enterprise Manager to view the following information about current Microsoft®
SQL Server™ activity:

Current user connections and locks.
Process number, status, locks, and commands that active users are running.
Objects that are locked, and the kinds of locks that are present.
If you are a system administrator, you can view additional information about a selected process,
send a message to a user who is connected currently to an instance of SQL Server, or terminate
a selected process.
Use the current activity window in SQL Server Enterprise Manager to perform ad hoc monitoring
of an instance of SQL Server. This allows you to determine, at a glance, the volume and general
types of activity on the system, for example:
Current blocked and blocking transactions.
Currently connected users on an instance of SQL Server and the last statement executed.

Locks that are in effect.
SQL Server activity can be monitored using the sp_who and sp_lock system stored procedures.
Here are icons and descriptions of the icons in the current activity window.
Icon Description
Current Activity gives process and lock information at a designated time. This
information is a snapshot taken every time you open or refresh Current Activity. The time

of the snapshot is displayed in the left pane. Current Activity provides information about
the processes (connections) running, the locks a certain connection is holding or trying to
acquire, and the current and waiting locks on databases and tables.
Process Info provides information about the current connections and activity in a system.
A connection can be in three states: running, sleeping, or background. The database
context is also displayed. There are some server processes, which are started before the
master database is brought online, that have no database context.
Running process that is waiting for a lock or user input.

Sleeping process that is waiting for a lock or user input.

Background process that wakes up periodically to execute work. SPID 2 (Lock Monitor), 3
(Lazy Writer) and 6 are background processes.

Process (SPID) that is blocking one or more connections.

Process (SPID) that is blocked by another connection.

Process that is not blocking or being blocked.

Process that is not blocking or being blocked.

Table lock. If an index is involved, the index name is listed in the index column. The
resource locator of the locked part is displayed in the resource column.
Database lock.

170 LOVELY PROFESSIONAL UNIVERSITY