sp_who2 shows the block by user and some information.Currently we are moving from sql 2000 to sql 2005. if i execute the sp_who2 in sql 2005 it shows only my login block information. it does not show other users. is there any admin rights has to give to view other block user ??? how to do that.
View 7 RepliesWhe we execute sp_who2 on a SQL server
Command column displays the following:Any idea on what is CONDITIONAl??
SIGNAL HANDLER
LOCK MONITOR
LAZY WRITER
LOG WRITER
CHECKPOINT SLEEP
AWAITING COMMAND
BULK INSERT
CONDITIONAl
Thanks.
Di.
DiskIO: Any idea what exactly this tells on sp_who2 output? Does that men anything if its high/low?
Thanks.
Ravi.
Is there any way to calculate (just an approximation is fine) theamount of work that is done for an insert statement?For example, can I calculate the approximate row size of my table andthen compare that with the DiskIO for my SPID to determineapproximately how many rows have already been written? Or, does theDiskIO include shuffling data around or other DiskIO that makes thiskind of comparison impossible?Thanks,-Tom.
View 1 Replies View RelatedThanks, folks, for taking time to help!In query analyzer and profiler, there seem to be system processes thatare generating high cpu time and disk io. Well, I guess the short ofit is that I am having a problem interpreting what I am seeing so thatI can take the next step in corrective action. There are also severaldomain controlled logins that appear to be on the same host name, atthe same time--these are laptops, not terminal servers--why would thesystem be reporting such a thing?Here is a sample of the output from sp_who2:SPIDSTATUSLOGINHOSTNAMEBLKBYDBNAMECOMMANDCPU TIMEDISKIOLASTBATCHPROGRAMNAME1BACKGROUND sa . . NULLLAZY WRITER75006/3/2005 5:142sleeping sa . . NULLLOG WRITER1025006/3/2005 5:143BACKGROUND sa . . masterSIGNAL HANDLER1606/3/2005 5:144BACKGROUND sa . . NULLLOCK MONITOR20306/3/2005 5:145BACKGROUND sa . . masterTASK MANAGER01516/3/2005 5:147sleeping sa . . NULLCHECKPOINTSLEEP243822396/3/2005 5:1410BACKGROUND sa . . masterTASK MANAGER0256/3/2005 5:1411BACKGROUND sa . . masterTASK MANAGER01636/3/2005 5:1412BACKGROUND sa . . masterTASK MANAGER0706/3/2005 5:1413BACKGROUND sa . . masterTASK MANAGER01556/3/2005 5:1451sleeping saMyServer . msdbAWAITINGCOMMAND683371386/8/2005 13:00SQLAgent - Generic Refresher52sleeping saMyServer . msdbAWAITINGCOMMAND1746266/8/2005 13:00SQLAgent - Alert Engine53sleeping DomainUser1LT1 . VPNMasterAWAITING COMMAND1687546/8/2005 12:31Crystal Reports54sleeping saMyServer . VPNMasterAWAITING COMMAND377296/8/2005 10:49MS SQLEM55RUNNABLE DomainUser2MyServer .masterSELECT 86006/8/2005 12:40SQL Profiler56RUNNABLE DomainUser2LT2 . VPNSELECT34496/8/2005 12:50Microsoft® Access57sleeping DomainUser3LT2 . VPNMasterAWAITING COMMAND110296/8/2005 12:52Microsoft® Access58sleeping DomainUser1LT1 .masterAWAITING COMMAND1172156/8/2005 9:31Crystal Reports59sleeping DomainUser4LT2 . VPNMasterAWAITING COMMAND1516/8/2005 12:50Microsoft® Access62sleeping DomainUser5LT2 . VPNMasterAWAITING COMMAND3226/8/2005 12:52Microsoft® Access63sleeping DomainUser6LT2 . VPNMasterAWAITING COMMAND50106/8/2005 12:52Microsoft® Access64sleeping DomainUser6LT2 . VPNAWAITINGCOMMAND101606/8/2005 12:52Microsoft® Access65sleeping DomainUser6LT2 . VPNMasterAWAITING COMMAND47496/8/2005 12:52Microsoft® Access66sleeping DomainUser3LT2 . VPNAWAITINGCOMMAND20306/8/2005 12:50Microsoft® Access67sleeping DomainUser6LT2 . VPNAWAITINGCOMMAND24906/8/2005 12:52Microsoft® Access68RUNNABLE saMyServer . masterSELECTINTO 62126/8/2005 12:59SQL Query AnalyzerThanks,Eric
View 2 Replies View RelatedThe behavior seems to have changed on SP_WHO2 in SQL 2005.
In SQL 2000 I could be logged in as a regular user in my system and run SP_WHO2 and get all the users currently logged in. This is no longer working in SQL 2005. It now only returns "me".
What kind of rights do you need to have to see who is logged in?
In the sql server analyzer, i ran sp_who2 store procedure to find out the procesess and locks on the sql server. I noticed on the status column some entries "RUNNABLE", does this could trigger blocked to other processess if not closed by our application? or What does runnable status mean?
View 1 Replies View RelatedDear All,
i've a doubt regarding the difference between sp_who and sp_who2.
i've read in one article stating that sp_who is documented and sp_who2 is un documented.what is meant by this?
and please provide me the articles related to locks(what are different types of locks)
thank you very much
Vinod
Even you learn 1%, Learn it with 100% confidence.
Hello,
When I run sp_who2, the blkby col shows spid -2.
From doing some reading I understand this is caused by SQL waiting on MSDTC.
If I look at MSDTC running transactions I can see 2 transactions, the transaction are not mark as indoubt so I cannot commit, abort or forget them.
If I stop ALL the Processes that are using the DB, eg IIS and a 3rd party workflow engine, these transactions do not go away.
Anyone got a clue?
If I restart MSDTC, the blocks go away in SQL but this is not a solution to the problem.
All MSDTC gives me is the ID for the transaction, can I get more info on whats happening from this ID.
Thanks
Steve
Hi!
I want to extract the same information as the Activity Monitor does with SQL in SQL Server 2000.
Have tried to use sp_who2 but I miss some important columns.
How do I do?
Thanks!
am experiencing excessive SSB thread block'n...sql error log is reporting LOTS of Resource Monitor messages about non-yielding threads (nothing meaningful can be surmised from it).
I am running on a 4way 64bit 2003 box w/6gb ram!!!
SSB architecture is simple implementation... Leveraging async trigger(s) in 42 db's (all on same instance) that post (via srvc) into a mstr db queue...where a listener is pull'n them off and applyies to a table (trying to avoid excessive 1205's that I was experiencing using sync trigger approach before)....messages sit in respective db's trans queue and draining of queues is extremely SLOW!!!! I mean SLOW!!!
Eventually SqlServer.exe process pegs out ALL processors!!! Only can reboot box to get connectivity back...~
Anyone have this experience!? (really hope not...but I need help)
Have completely cycled SSB machinery (via disable/enable)...and have even stepped thru enabling one db at a time...but still very poor performance!!!
Anyone?
-mt
sp_who output here...
BACKGROUND sa . 16 NULL RESOURCE MONITOR
BACKGROUND sa . . NULL LAZY WRITER
SUSPENDED sa . . NULL LOG WRITER
BACKGROUND sa . . master SIGNAL HANDLER
BACKGROUND sa . . NULL LOCK MONITOR
sleeping sa . . master TASK MANAGER
BACKGROUND sa . . master TRACE QUEUE TASK
sleeping sa . . NULL UNKNOWN TOKEN
BACKGROUND sa . . master BRKR TASK
BACKGROUND sa . . master TASK MANAGER
SUSPENDED sa . . master CHECKPOINT
sleeping sa . . master TASK MANAGER
sleeping sa . . master TASK MANAGER
BACKGROUND sa . 16 ThompsonTractorD43 KILLED/ROLLBACK
sleeping sa . . master TASK MANAGER
BACKGROUND sa . . master KILLED/ROLLBACK
BACKGROUND sa . 16 master KILLED/ROLLBACK
sleeping sa . . master TASK MANAGER
BACKGROUND sa . . master BRKR TASK
BACKGROUND sa . 16 master BRKR TASK
sleeping sa . . master TASK MANAGER
sleeping sa . . master TASK MANAGER
sleeping sa . . master TASK MANAGER
sleeping sa . . master TASK MANAGER
BACKGROUND sa . 16 YancyMachineryCat KILLED/ROLLBACK
BACKGROUND sa . . master BRKR EVENT HNDLR
BACKGROUND sa . . master BRKR TASK
sleeping NT AUTHORITYSYSTEM REFINERY1 . msdb AWAITING COMMAND
sleeping NT AUTHORITYSYSTEM REFINERY1 . msdb AWAITING COMMAND
sleeping NT AUTHORITYSYSTEM REFINERY1 . msdb AWAITING COMMAND
sleeping NT AUTHORITYSYSTEM REFINERY1 . msdb AWAITING COMMAND
SUSPENDED NT AUTHORITYSYSTEM REFINERY1 . msdb DELETE
sleeping fastironweb DETROIT . Cat_Lvl3 AWAITING COMMAND
sleeping mike REFINERY1 . master AWAITING COMMAND
SUSPENDED NT AUTHORITYSYSTEM REFINERY1 . distribution WAITFOR
sleeping mike REFINERY1 . Cat_Cfsc AWAITING COMMAND
sleeping mike REFINERY1 . Cat_Cfsc AWAITING COMMAND
sleeping mike REFINERY1 . Cat_Cfsc AWAITING COMMAND
RUNNABLE mike REFINERY1 . Cat_Cfsc SELECT INTO
sleeping NT AUTHORITYSYSTEM REFINERY1 . msdb AWAITING COMMAND
I've got a new server with Pentium4 xeon processors. I've used sp_who2for quite a while on my old server -- to look at CPU and IO usage. Onthe new server, the CPU times are huge -- although actual performanceis quite good. I know sp_who2 is an undocumented proc. Has anyoneexperienced this or is it just me?
View 1 Replies View Related