Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [sp-whoisactive]

For questions about the SQL Server monitoring stored procedure written by Adam Machanic

34 questions
9
votes
1 answer

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? Most of the time, the query_plan value is NULL. If…
SomeGuy
  • 2,053
  • 8
  • 35
  • 48
8
votes
1 answer

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 sql_command (the column shown when…
SE1986
  • 2,142
  • 4
  • 30
  • 61
6
votes
1 answer

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 procedure. I get an error I'm quite familiar with…
RandomCorpSQLDev
  • 63
  • 4
6
votes
1 answer

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 things I did was create the ability (thanks to Brent,…
Randoneering
  • 135
  • 9
6
votes
1 answer

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 for node 'Lock/@resource_description' because it…
kevinnwhat
  • 2,224
  • 1
  • 10
  • 21
6
votes
1 answer

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... Can anyone explain what, if any difference…
SE1986
  • 2,142
  • 4
  • 30
  • 61
4
votes
1 answer

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?
Tony Hinkle
  • 8,062
  • 1
  • 24
  • 46
4
votes
2 answers

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 scheduled intervals. Executed as user: Warning: Null…
LeraningDBA
  • 331
  • 1
  • 3
  • 16
3
votes
1 answer

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 people using company's app and logged in under same…
Serdia
  • 707
  • 1
  • 8
  • 16
3
votes
1 answer

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 millions of reads, but when I run the eight…
Tony Hinkle
  • 8,062
  • 1
  • 24
  • 46
3
votes
1 answer

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 sp_WhoIsActive to a table, and below is the lead blocker. I…
SomeGuy
  • 2,053
  • 8
  • 35
  • 48
3
votes
1 answer

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 increase over time. I have hundreds of these at any…
Halt_And_Catch_Fire
  • 161
  • 1
  • 15
3
votes
2 answers

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 strange. I check if someone is using the database…
Racer SQL
  • 7,546
  • 16
  • 77
  • 140
2
votes
1 answer

Insert result of sp_WhoIsActive in a remote table on remote sql server machine

Adam's script has been a great help to DBA community. Below is the query that we want to run from sql server 'SERVERA'; sp is at 'SERVERB' also the table 'tbl_whoisactive' is at 'SERVERB' Below script is run SELECT a.* From openrowset('SQLOLEDB',…
user206045
  • 21
  • 2
2
votes
0 answers

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; BEGIN TRANSACTION; -- …
gotqn
  • 4,348
  • 11
  • 52
  • 91
1
2 3