You work as a database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. The Domain.com network contains a SQL Server 2005 database server named Certkiller -DB01.
Certkiller -DB01 contains a database named CK_Sales that is used to process sales orders via an order processing application. You received a report from a Domain.com user named Rory Allen informing you that the order processing application stopped responding in the middle of an order transaction. Rory Allen’s SQL Server session ID is 54.
You need to determine if session 54 is blocked by another connection. If the result is positive, you need to find the blocking session ID. What should you do? (Each correct answer presents a complete solution. Choose TWO.)
A.
In Activity Monitor, view the BlockedBy column for session 54 on the Process Info page.
B.
In Activity Monitor, view the Request Mode column for session 54 on the Locks by Process page.
C.
Open a new query window and run the statement: SELECT * FROM sys.dm_exec_requests WHERE session_id = 54. Then view the blocking_session_id column.
D.
Open a new query window and run the statement: SELECT * FROM sys.dm_exec_sessionsWHERE session_id = 54. Then view the status column.
Explanation:
The Process Info page shows the properties of current processes. Blocked By tells you the Process ID (SPID) of a blocking process. Blocking indicates whether this process is blocking others. 1=yes; 0=no. The sys.dm_exec_request DMV can be used to determine whether a process is being blocked and to identify the process that is creating the blocking. If a value greater that 0 exists in the blocking_process_id column, the process is being blocked by the SPID logged in the column.