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 RepliesIn a development database, a developer was running 11 simultaneous jobs (in
PB) doing inserts into 11 tables. Ten completed and he wanted to know which
one was still active. We suspected it was on the largest table with the
most inserts was the one still active.
We're running on MSSQL 6.5, sp4.
We looked at current activity -user activity and saw that there were two
active users. Myself and someone else but not the developer. Although one
hint was a small blue line in the icon of a red page surrounded by a red
circle and a red line going through it, I guess that was the indication of
which table.
When we logged onto ISQLW and ran sp_who on are separate machines as sa, we
saw different statuses on the same process.
I saw the process 31 as sleeping and the developer on his machine(also
logged on as sa) saw the process 31 as runnable?
My question is this correct? How do I tell if HIS process is running if I
can't see it on my machine when we are both logged on as SA?
Any help will be greatly appreciated. Thanks.
David Spaisman
Hi there,
Currently using SQL Server 2000 (SP4). The following condition started occurring last week:
- Server has excessive blocking
- Majority of the processes are in runnable state
- Excessive blocking happens for a few mins. and repeats again during the day. Does not happen at night.
- Nothing on the server errorlog, profiler
- CPU averages 40 - 50% at that point of excessive blocking
Any help would be greatly appreciated.
Thanks.
Hi,Database Server running slow. I used SP_WHO2 ACTIVE and the resulthas many ofXXXX SA RUNNABLE MASTER AWAITINGCOMMAND..........(XXXX is spid)and they stay for couples seconds.Last week , the server was running fine and during weekend ,noneof errors record in Windowsevent log or SQL Server log.Server is Windows 2003 server with 4 CPUs of xeon , 4 GB of memoryandMSSQL SERVER 200 SP4.I found out that every runnable master db tasks has shared lock onkey of'master.dbo.sysxlogins.sysxlogins' objectIs this the problem of slow running database server?Please advice.Peeud
View 1 Replies View RelatedHello all,I'm using SS 2000 and NT4 (and Access97 as front-end on another server)Well, probably by lack of knowledge about table locks, I don't really knowwhere to start to present this problem. In Enterprise manager, section"Management->Current activity->Locks/Objects", we have a couple (5-7) of"forever runnable" processes, all related to two specific situations. Eachof them are for "SELECT" statements. It's been a long time since it's likethat. I've always been curious about them but the weren't causing anyproblem. Now, after a modification, a third situation happened ("SELECT"again)... and sometime a lock created by this new "forever runnable" processblocks other functions that use the same table. All my table are linked withan ODBC link.Any help or suggestion where to search would be appreciated.Thanks.Yannick
View 1 Replies View RelatedWhe 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.
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 Replies View RelatedDiskIO: 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?
Dear 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 RelatedHi Experts,
I am extracting data from SQL Server 2005 to flat file destination. I am using SQL Command to specify the data selection query. One of my query uses Replicate function to derive a column value. When I execute this package it fails with the error "Data conversion failed. The data conversion for column "value" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page".
The reason for the problem is that, it is taking the InputColumnWidth of the flat file destination as 8000 and I specified the OutputColumnWidth as 4.
If I change the OutputColumnWidth to 8000, it is working without any error but resulting in the column width of 8000.
I tried using DerivedColumn Transformation's Type cast and DataConversion Transformation but still I am getting the same error in the respective Transformation components.
Can anyone suggest how to solve this issue.
Returning "completed" when status = 1 and "not completed when status = 0
View 3 Replies View RelatedSQL7, sp3, NT4...
Unless I grant a developer "system admin" role, they are unable to see whether or not any job *not* owned by themselves is running?
EX: Developer A can't see whether Developer B's job is executing? Why? I've granted them both db_owner in msdb, and all permissions possible in msdb. Is there an extended sp I can look for?
Thanks,
Hi Gurus,
can any one help me to get the job status of a sql job.
My requirement is as under
ordinary user (not SA) will query pass job name as argument to stored procedure and sp has to give me the job status.
Thanks in advance
I have a table with columns c1, c2, c3, c4.if all nulls or blanks. Status = 0if c1 assigned but no c2, c3, and c4, then status = 1if c2 assigned but no c3 and c4, then status = 2if c3 .. then ..if c4 .. then ..I want to have one SQL to get the status like (ignored checking forblanks here for demo)SELECT Status = (if not c4 is null then 4else not c3 is null then 3else not c2 is null then 2else not c1 is null then 1else 0)FROM mytable.Thought of using CASE ... WHEN ... but it is only on one colum.Any better idea.ThanksJohn
View 1 Replies View RelatedIn SQL 2000, go to Management, than Jobs, you will see a list of all jobs and their running status. I would like to programmatically display the same on my web page. I can call sysjobs table to get the jobs and use sysjobschedules to determine whether it's been scheduled or not. Then use sysjobhistory to obtain their running history. But how and where do I get the Next Run Date information? Is this calculated or stored somewhere?Can anyone give me some help on how to work with sysjobs, sysjobschedules, and sysjobhistory tables? All I am trying to do is to build a web-based app that does the same thing as the Enterprise Manager is doing - create and schedule a job, and monitor the job status.Thanks a lot!Penn
View 1 Replies View RelatedHi all - am having trouble working out what would be the best way to do this (most efficient)I have written an order, despatch and accounts system. To get a list of orders ready for despatch, I have written a query which calculates the amount of items ordered against the amount of items despatched from that order. If there are any outstanding items, then this order is in the list of orders which are outstanding for despatch.
I am having trouble working out how to go about creating a list of orders ready do be invoiced... Orders that are ready to be invoiced can be completely despatched orders or part despatched orders. That is to say that if an order is only half despatched, my client still wants to be able to raise an invoice against that part of the order. I envisage creating a query which checks my orderline table to see if the item has been invoiced and creating the list from there. But as the application is used more and more, this query will take longer to run? ANy ideas?
Thanks.
Steve.
Hello,
Can anyone guide me to monitor whether Production Sql server is up and running and would e-mail or page me if by chance it has stopped.
Any help is appreciated.
Thanks.
Priti
is there any way to make the status of a package execution to be success without caring about failure of certain steps in the package?
View 1 Replies View RelatedI need to know the meaning of the status bit in sysdatabases because BOL only reports some of them.
For SQL 7.0 SP1 I have some database with a status bit equal to 0 (zero) and equal to 12.
For SQL 2000 SP2 I have some database with a status bit equal to 0 (zero),24,20 and 1073741840.
So I would really appreciate if someone can explain the meaning of these status bits.
Thank you very much.
Franco
Hi,
Is there any way to update the status bar in Query Analyzer.
Suppose I am running a script which updates data to a file. I want to know the status showing the percentage it completed.
Thanks
:confused:
I just started working with MS SQL. I'm attempting to find the equivalent of the MySQL commands STATUS and SHOW STATUS.
Essentially, I want to connect to the database from a .net app, check the status, such as how many connections, table locks, errors, etc... and display that information on my application.
Any recommendations?
I have a question I hope someone can help me with.
My situation:
I have a single-step job in SQL Server, which runs a stored procedure, A. This stored procedure invokes another stored procedure, B. In B, one of the statements is a 'BACKUP DATABASE' command, and a database is backed up to a file.
The job is started by an application. Once started, the application then uses the SQL-DMO property CurrentRunStatus to periodically check the status of the job. When the property returns the value SQLDMOJobExecution_Idle (indicating the job has completed), the application code then continues processing, and attempts to access the .dat file produced by the 'BACKUP DATABASE' command.
My problem:
On occasion, the application will hit the problem where either the backup file cannot be located, or the file is still being locked by another process (Error=The process cannot access the file because it is being used by another process).
Is anyone able to shed some light on this?
I assume that the job will only return a completed status after:
(a) both A and B have completed execution, and
(b) the BACKUP operation has completed
Is it possible that even though SQL Server indicates the job has finished, that the BACKUP operation still hasn't completely ended?
Thanks for any help,
Andrew
Hi
My boss and I have recently started designing a new database, and we've got into a bit of a debate about creating status tables (e.g. order statuses).
It was my intial instinct to create a separate table for each kind of status (e.g. an OrderStatuses table, a JobStatuses table, etc), however my boss wanted to create one table containing all the statuses, with an extra field for defining what kind of status it is.
I was just wondering what others thought was the best approach.
Thanks
Tom
Hi Gurus,
While iam connecting to servers through management studio iam facing
iam seeing like few servers are connected with Green colour (like>)and few are connected with circle as white.Frm these how can i decide the server status.
Is there any comand to view server status ?
Thanks,
ServerTeam
Hello All,
Can someone help me with this query?
I have the following table and data structure
Table1: Orders
Field1: OrderID
Table2: OrderStatus
Field1: OrderStatusID
FIeld2: OrderID
Field3: StatusID
Field4: InsertDate
Sample Data:
Table1:
11
12
Table2:
1,11,1,6/1/2008
2,12,1,6/1/2008
3,11,2,6/2/2008
4,12,2,6/2/2008
5,11,3,6/3/2008
6,11,4,6/4/2008
Wanted Results:
11,4,6/4/2008
12,2,6/2/2008
How can i exclude those with more than one status? i tried using rows but it seems doesn't work.
ID status
1 new
1 new
1 close
2 new
2 close
3 close
3 close
4 new
4 close
the result should appear as:
ID status
2 new
2 close
4 new
4 close