Stack Overflow
Stack Overflow

Questions tagged [sp-who2]

In Microsoft-SQL-Server sp_who2 is a stored-procedure that provides information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine.

In Microsoft sp_who2 is a that provides information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine. The information can be filtered to return only those processes that are not idle, that belong to a specific user, or that belong to a specific session.

Sp_who2 returns the following informations:

  • Spid: System process id that requested the lock
  • Status: Background, sleeping or runnable
  • Login: The login name that has requested the lock
  • HostName: The computer where the lock request has been initiated
  • BlkBy: The spid of the connection that is blocking the current connection
  • DbName: The database name where the lock request has been generated
  • Command: General command type that requested the lock
  • CPUTime: The number of milliseconds the request has used
  • DiskIO: Disk input / output that the command has used
  • LastBatch: Date and time of the last batch executed by the connection
  • ProgramName: The name of the application that issued the connection
  • Spid: In it is difficult to read the spid from the beginning of the output it is repeated here
14 questions
12
votes
2 answers

sp_who2 BlkBy Sleeping Process Awaiting Command

When running sp_who2, it appears one of my SQL commands is blocking but waiting on a process that is "Sleeping" and "Awaiting Command". This doesn't make any sense. Any ideas what might be causing this? I know the DELETE is running inside a…
Stefan Mai
  • 23,367
  • 6
  • 55
  • 61
9
votes
2 answers

SP_WHO2 conditional?

I've got a long running insert and periodically when I run SP_WHO2 it shows "CONDITIONAL" as the Command. What would that be indicating? When I searched for this I found most things saying something like, 'yep, you see that when you have a long…
Chris Townsend
  • 3,042
  • 27
  • 31
3
votes
1 answer

History of connected servers/clients to a SQL Server database

Is there a way in SQL Server to see the history of all connected servers / clients with their respected hostnames, IP addresses and the last connected date? Basically what I want is an history table of the sp_who2 stored procedure. Does this or…
Chester van Ree
  • 105
  • 8
1
vote
1 answer

VB.Net SQLClient Connections and SQL Server - active connections reflect even though all closed

Using VB.Net and SQL Server 2008 R2: I have recently had a database that using sp_who2 reflects a connection ".Net SqlClient Data Provider" is still active even though the .Net application has closed and disposed of all connections. The code looks…
Raphael Segal
  • 11
  • 1
  • 2
1
vote
0 answers

Block in SQL Server (SP_WHO2)

I have a problem with the company I work in, some people are using SQL in the wrong way causing blocks that naturally crash the whole system, but we are not able to catch who is doing this, because when perceiving the person for the process. I…
Gabriel Sarzi
  • 23
  • 3
1
vote
2 answers

Show output of sp_who2 in WPF datagrid

I wish to display the output of the SQL Server command "sp_who2 active" in a WPF datagrid. I've come up with the following code - private void GetActiveSQLIds() { SqlConnection con = new SqlConnection(STR_DataSource); …
Craig Schwarze
  • 11,367
  • 15
  • 60
  • 80
1
vote
1 answer

SQL Server `sp_who2` procedure shows long locks by a sleeping connection

I'm running an Azure SQL Server and have some issues with the WebApi application which is using it. At some point under heavy load, the app starts to create lots of connections and sp_who2 shows that I have several long locks. After following the…
Mando
  • 11,414
  • 17
  • 86
  • 167
1
vote
1 answer

EXEC sp_who2 LastBatch showing future date

I am receiving future dates in LastBatch column when i run EXEC sp_who2 on SQL Server. for example most of the SPIDs have 5/14 as LastBatch date. (which means 2014) And my first 20 row has 04/28 as LastBatch date. (which means 2028) Any idea why i…
thatthing
  • 676
  • 3
  • 15
  • 39
1
vote
1 answer

why does "SELECT 1 from " cause a LCK_M_IX on another process doing a DELETE
I have a table listing patient_clinic_visits. I have SQLSERVER 2005 backend. Access2010 frontend. Each morning this table needed refreshing from a data dump from the hospital mainframe. this data dump includes information on the day before (who…
dmc2005
  • 546
  • 1
  • 4
  • 14
0
votes
1 answer

How can I tell if a host is connecting via multiple spids using one application or running multiple instances of the application itself?

We trace our SQL Server activity for performance issues. Some applications, written in C#, look to have up to 50 individual spids connecting to the DB from a single machine. How can I distinguish if these spids are connecting from multiple…
cloudsafe
  • 2,444
  • 1
  • 8
  • 24
0
votes
1 answer

sp_WhoIsActive/spwho2 - get process id

I am a developer helping the DBAs with a sporadic CPU 100% issue. Please see the SQL query below: Select spid,hostname,hostprocess,program_name,nt_username, blocked, waittime, waittype,…
w0051977
  • 15,099
  • 32
  • 152
  • 329
0
votes
0 answers

LastBatch in result of sp_who2 is set to 01/01

I executed stored procedure sp_who2 'Active'. In result I got list of active connections to db. But I also noticed that there are some connections where LastBatch equals to 01/01. What does it mean? There are some queries that is never executed,…
demo
  • 6,038
  • 19
  • 75
  • 149
0
votes
0 answers

Delete Statement runs forever

I am facing an issue while trying to delete a set of records from a table. When I run the delete statement it ends up locking the table and hence blocking all other operations on that table. Only with NoLock hint I am able to view the result sets…
Prakazz
  • 421
  • 1
  • 8
  • 21
-5
votes
1 answer

SQL Server CPU Usage

How to find CPU usage idle, user, system, IO for Analyses using SQL Query in SQL Server
Rakesh
  • 261
  • 1
  • 2
  • 15