SQL Server Live Activity
Use Live Activity to see what runs on one SQL Server instance now, find who blocks whom, and read the deadlocks.
Before you begin
- You need the View SQL Server and View Live Activity permissions. Open the page with Live Activity in the database bar, or click Active requests on the Dashboard.
- The page only reads data. It does not kill sessions.
- To show one database, select it next to the name of the instance (see Show one database).
Find a request
The Sessions tab lists the requests of the newest sample, the longest running first. A session that blocks other requests, but does no work, is also in the list.
To show only some values of a column, click the filter icon in its header and select the values.
To sort the list, click a column header.
To add columns, such as Tempdb or Transactions, click Columns. Click Save to keep your choice.
The line above the list counts the requests, their tasks and the blocked requests.
What the marks mean
| Mark before the session | The session |
|---|---|
| Green dot | Runs a request that is part of the load. |
| Amber triangle | Blocks other requests, but is idle, or its request is not counted as load. Hover over its Waiting On to see why. |
| Red diamond | Is blocked: it waits on a lock of another session. |
| Tag | Meaning |
|---|---|
| system | A system session: the work of SQL Server itself. |
| DOP 4 | The request runs in parallel, with 4 tasks at most. |
| blocks 3 | 3 requests wait behind it, directly or through other sessions. |
| 12 requests | Requests that are alike, kept as one row. Scops does this when a sample has more rows than it keeps one by one. |
What the columns show
| Column | What it shows |
|---|---|
| Elapsed | How long the request runs. For an idle session, how long it is idle. |
| Statement | The query hash. Click it to open the statement details. An idle session shows its last batch. Work without a query hash shows its command. |
| Waiting On | The wait type, with the color of its wait category. Or On CPU, CPU queue, Idle, or Idle, transaction open. |
| Wait | How long the request waits. For a blocked request, amber from 1 minute and red from 5 minutes. |
| Blocked By | The session that holds the lock. |
| Memory Grant | The memory grant that the request holds. waits for, in amber, means that it waits for its grant. |
| Transactions | The open transactions, and how long the oldest is open. |
Refresh the data
Each tab shows the time of its data (Sampled) and has a refresh button. To refresh the open tab automatically, open Auto Refresh Off at the right of the tab bar. Select Every 5 s, Every 15 s or Every 60 s. The default is Off.
Look back with History
Click History.
Set the start and the end, one hour apart at most. The default is the last hour up to the newest sample.
Click Run.
On Sessions, each row is one request in one sample, the newest first. If the interval has too many rows, Scops lists the newest: shorten the interval to see the rest.
Blocking and Long Ops show the sample at, or just before, the end of the interval. To go to the sample before or after it, click the arrows next to Run. To go back to now, click Real Time.
A past sample has no memory grants, tempdb, transaction ages or locked objects. Scops reads them only for the newest sample.
Blocking
The Blocking tab shows each lock wait as a chain. The head of the chain waits on nobody, and the requests under it wait. The chain with the most requests behind it is first.
| Column | What it shows |
|---|---|
| Waiting | How long the request waits on the session above it. Amber from 1 minute, red from 5 minutes. |
| Waiting On | For the head: what it does, or Idle and since when. |
| Locked Object | The object that the request waits on. If Scops cannot name it, the wait resource of SQL Server. |
| Transactions | The open transactions of the session, and how long the oldest is open. |
To hide or show the requests behind a session, click the arrow before it. Under each session, the chain lists the 50 requests that wait longest and counts the rest. Below the eighth level, a chain shows flat, with a level tag.
The tab shows the number of blocked requests. It is amber when the longest wait is 1 minute or more, red from 5 minutes.
Deadlocks
The Deadlocks tab lists the deadlocks that SQL Server ended, from its system_health session. Scops reads them once a minute. SQL Server writes a deadlock to its files within two minutes.
Select the period: 1D, 1W or 1M.
To show only one bar, click it in Deadlocks over time.
To show only one database, click it in By database.
To clear a choice, click its chip.
| Column | What it shows |
|---|---|
| Last Seen | The last time that SQL Server found this deadlock. |
| Count | How many times it occurred in the period: the same statements on the same objects. |
| Victim | The session that SQL Server ended. |
| Other Session | The other side. +1 more means that more sessions were in the deadlock. |
The tab shows the number of deadlocks of the last hour. When you show one database, the second card is By object. Scops keeps the deadlocks for 30 days by default (see Retention).
Read a deadlock
On Deadlocks, click a row. The deadlock panel opens.
Compare the sides. Each side shows its login, application, host, database, isolation level, the locks that it waited for and held, its transaction and its statement.
To open the deadlock graph in Management Studio, click Save graph. Scops saves an
.xdlfile.
Victim marks the side that SQL Server ended with error 1205. Survivor marks a side that continued. Resources lists the objects of the deadlock.
Long Ops
The Long Ops tab lists the commands that report their progress, the next to end first. SQL Server reports the progress of some commands only, such as backups, restores and DBCC checks.
Expected End is the time when SQL Server expects the command to end, on the clock of the instance. If SQL Server gives no time, it shows no estimate.
Related
Updated November 22, 2026