Questions tagged [sp-whoisactive]
For questions about the SQL Server monitoring stored procedure written by Adam Machanic
32 questions
0
votes
1
answer
251
views
sp_whoisactive is showing a Blocking Session ID of -5
We're in the process of upgrading to SQL Server 2022. While comparing performance between our old QA server (SQL 2014) and our new QA server, we're using sp_whoisactive to help identify bottlenecks. ...
- 105
-4
votes
2
answers
259
views
Why Prefer Extended Events to sp_WhoIsActive and Query Store?
If I want to find out what is happening right now, then I use Adam Machanic's sp_whoisactive. If I want to find out what has happened on my server recently, then I will use Query Store.
Extended ...
- 799
1
vote
1
answer
390
views
SET SINGLE_USER stuck on wait type PRINT_ROLLBACK_PROGRESS
I want to drop a database on one of our systems (SQL Server 2016 SP2 CU17), but the process to set the database in single user mode is stuck on the wait type PRINT_ROLLBACK_PROGRESS. I use the ...
- 529
1
vote
1
answer
437
views
How to Get a PAGELATCH_SH in sql SERVER tempdb
I am learning memory optimization in SQL server . On the way I learned about PAGELATCH WAIT .But I don't know how it happens and I don't know how to Test it with optimisation .
Can someone give me an ...
- 99
5
votes
1
answer
537
views
CTE Error when installing sp_WhoIsActive
I have several identical (near as I can tell) SQL Servers where I've recently added sp_WhoIsActive (showing some folks how much I like this tool) but one of them will not let me create the stored ...
2
votes
0
answers
119
views
Are tempdb allocations released after transaction is commited?
In addition to this I am again wondering if the code in a transaction and loop release the tempdb allocations. For the template below:
SET NOCOUNT ON;
WHILE EXISTS(....)
BEGIN
BEGIN TRY;
...
- 4,318
0
votes
1
answer
223
views
Retrieve execution plan via XML data from sp_WhoIsActive in adhoc process
We have installed a process in which we execute the sp_WhoIsActive procedure every 1 minute. We combine some additional logic into the same job.
Currently the sp_WhoIsActive procedure is run every 4-5 ...
- 421
0
votes
0
answers
124
views
Connect query from sp_whoisactive and Query Store
We have application which is returning result of sp_whoisactive every 1 minute. Is that any option to connect (join) results from sp_whoisactive and QueryStore ? I would like to add some stats from ...
- 421
0
votes
0
answers
39
views
sp_WhoIsActive CREATE statement in sql_text column while executing object inside another [duplicate]
I am running fabulous sp_WhoIsActive with these parameters:
EXEC dbo.sp_WhoIsActive
@get_task_info = 2
,@find_block_leaders = 1
,@get_outer_command = 1
,@get_transaction_info = 1
,@...
- 945
0
votes
0
answers
507
views
Get actual Job Name of the running SSIS package from Program Name column of the results while running sp_whoisactive or sp_who2
When I run sp_whoisactive or sp_who2 and if there is a SSIS Package running it would always display in the Program Name as the name of the running SSIS Package like SSIS-Package-{063BC46B-1F74-4207-...
- 47
2
votes
3
answers
5k
views
Get actual Job Name from Program Name column of the results while running sp_whoisactive or sp_who2
I am running sp_whoisactive on my Prod and offload the data for analysis.
When I run sp_whoisactive or sp_who2 and if there is any SQL Agent Jobs running it would always display the Program Name like ...
- 47
1
vote
2
answers
1k
views
How to fix error recording sp_whoisactive results to table?
Have a sql agent job modeled from here that uses sp_whoisactive to capture results to a table. 99% it works fine but every now and then the sql agent job will fail with this error:
Warning: Null value ...
- 1,127
6
votes
1
answer
629
views
Limiting sp_whoisactive output
All,
I am currently putting together a knowledge based article that we can hand to our helpdesk to run through in order to do some preliminary troubleshooting or information gathering. One of the ...
- 135
3
votes
1
answer
568
views
Is it possible to see blocker's host name or machine name using sp_WhoIsActive?
I am using sp_WhoIsActive.
When blocking is occurred on a server I want to quickly identify who is the culprit.
Column login_name does not give me much info about blocker because there are hundreds ...
- 707
6
votes
1
answer
619
views
sp_whoisactive @get_locks parameter xml error
I often receive this error when running the following command
sp_whoisactive @get_locks = 1
Msg 6841, Level 16, State 1, Procedure sp_WhoIsActive, Line 4287 FOR XML could not serialize the data ...
- 2,224
0
votes
1
answer
1k
views
Why does sp_whoisactive show locks on databases, but not tables?
Recently I have noticed some threads running on our sql server which are invoked from our application, which are in a sleeping status for a few minutes and have open transactions (usually just one).
...
- 2,224
0
votes
3
answers
7k
views
Database Blocking, kill the session which is responsible after 5 minutes
I have been struggling with database blocking at my database server from last so many months. Have tried to use many ways to capture them and also aware that who are leaders of blocking. This ...
- 3,904
0
votes
1
answer
1k
views
sp_WhoIsActive waiting resource
I have a huge query in SQL Server 2016 – when I run the query for a subset of records, it completes in 15 minutes. When I extrapolate it to total number of records, it is supposed to be complete in 1 ...
- 900
8
votes
1
answer
2k
views
Unfamiliar syntax - Query with Parameters in Braces at the beggining
I have run sp_WhoIsActive on one of our servers using the following syntax:
sp_whoisactive @get_plans = 1, @show_sleeping_spids = 0, @get_outer_command = 1, @get_locks = 1
and found a spid with the ...
- 2,105
6
votes
1
answer
1k
views
sp_WhoIsActive vs sp_BlitzFirst @ExpertMode = 1
I use sp_WhoIsActive and sp_BlitzFirst (with ExpertMode paramter set to 1) to get an overview of what is happening on the SQL Server in real time. I find these to be an upgrade on sp_who, sp_who2 etc.....
- 2,105
4
votes
1
answer
84
views
sp_whoisactive - Do stats include activity for snapshot versioning?
On a system that has read committed snapshot isolation enabled, do the reads, writes, CPU, etc. stats returned by sp_whoisactive include the activity associated with maintaining versions in tempdb?
- 7,984
3
votes
1
answer
409
views
sp_WhoIsActive: Are Stats Cumulative for Session or Batch?
In the output of sp_WhoIsActive, are columns such as reads, writes, CPU, and wait_info cumulative for the session, or just cumulative for the batch? We have a query that is blocking a lot and showing ...
- 7,984
2
votes
1
answer
2k
views
Monitor SQL Server blocked processes [duplicate]
I have been using sp_whoisactive and this is very helpful in finding details of running sessions and blocking.
I wanted to check whether there is any option to schedule a job based on this procedure ...
- 3,904
0
votes
1
answer
1k
views
sp_whoisactive time columns
I am looking at data captured with the help of sp_whoisactive.
session_id: 1016
blocked_session_count: 24
Login_time : 29/03/2018 12:14:59
tran_start_time : 29/03/2018 12:14:59
start_time: 29/03/...
2
votes
0
answers
65
views
ERP System Blocking Transaction - Not Sure If There Is A Fix
I am experiencing a problem with our ERP system that is causing my company a huge headache and I am not sure if there is anything that I can do to try to resolve.
Essentially, when someone at our firm ...
3
votes
1
answer
4k
views
Identifying why query is sleeping with open transactions
We have a 3rd-party application which is sporadically leaving connections in a sleeping state with an open transaction. This is causing many other queries to be blocked. I've been logging ...
- 2,053
3
votes
1
answer
603
views
Sleeping SPID with Open Transcations
This is an output from sp_whoisactive. Should I be worried about the sleeping spids with high cpu numbers and an open transaction? The sql_command value is the same as sql_text. CPU and reads do ...
3
votes
2
answers
442
views
Strange Situation with sp_WhoIsActive
I'm with a simple task to migrate databases.
I'm doing the backup in the primary database, setting it to OFFLINE, and then, migrating it to the secondary.
No problem at all.
but I notice something ...
- 7,484
2
votes
1
answer
638
views
Is there a way to get the isolation level from sp_whoisactive out of the xml into the plain result?
While I caught myself re-engineering the functionality of sp_who / sp_whoisactive using the sys.sysprocesses and sys.dm_exec_session views, to get an overview about running sessions and transactions ...
- 4,807
4
votes
2
answers
5k
views
Automated SP_WhoIsActive SQL Activity Capturing Issue
I have an SQL Agent Job on a production server that keeps failing with the below messages. It is supposed to be capturing SQL Server activity using the SP_WHOISACTIVE stored proc, at regularly ...
- 331
1
vote
1
answer
2k
views
Use sp_WhoIsActive in AWS RDS (MSSQL)
I'm trying to run Adam Machanic's sp_WhoIsActive in AWS RDS for SQL Server. When I run as the designated master account, say "TheAdmin", it works. When I run as another user, say "AlmostAdmin", whom ...
- 804
9
votes
1
answer
4k
views
When does an execution plan not show in sp_WhoIsActive?
I use Adam Machanic's excellent sp_WhoIsActive procedure to see activity and to log to a table to troubleshoot long running queries.
Why does the execution plan not show all the time in the results? ...
- 2,053