Problem With Spid By Sp_who

Nov 30, 2005

Hello 2 all,

Maybe my question can be very stupid, but I'm a little confused.
When I run sp_who on my database, I see one process (accessing remotely
my database from another database on another SQL server) many many
times.

Well, I assume that this is one process, because I cannot imagin that
the data aquiring can be done by almost 1000 (thousand) opened
connections.
This is what I have. Almost 1000 spids running (sleeping) with cmd =
awaiting command.

The best part is, that when I run sp_lock, I see, that the current
session of the client, (lets say spid 53) is locking tempdb, with
objects id below 10 (system).

As far as i know, and as far as I'm using this in my software, there is
no necessity to open-close-open-close connection.
Normaly, you can just open a connection to db, with assigned spid, and
operate within this just one. After the disconect ist done, the session
shoould be removed.

I think, there is something wrong in the way, the remote system is
connecting to my database, but i'm not sure is it normal what I'm
expieriencing or not.

below, a piece of that what sp_who returned:

SPIDECIDstatusloginnamehostnameblkdbnamecmd
2660sleeping remote_user_namehost_ERP 0
PRIMARY_PROCESS_DBAWAITING COMMAND
2670sleeping remote_user_namehost_ERP 0
PRIMARY_PROCESS_DBAWAITING COMMAND
2680sleeping remote_user_namehost_ERP 0
PRIMARY_PROCESS_DBAWAITING COMMAND
2690sleeping remote_user_namehost_ERP 0
PRIMARY_PROCESS_DBAWAITING COMMAND
2700sleeping remote_user_namehost_ERP 0
PRIMARY_PROCESS_DBAWAITING COMMAND
2710sleeping remote_user_namehost_ERP 0
PRIMARY_PROCESS_DBAWAITING COMMAND

and these are locks from sp_lock 53 which is the current running
process:
53700DB SGRANT
53260TAB IXGRANT
53210TAB IXGRANT
53230TAB IXGRANT
53220TAB IXGRANT
532120TAB IXGRANT
53290TAB IXGRANT
532110TAB IXGRANT

53232KEY(1902141b21c7) XGRANT
53213KEY(e60041ad2c6f) XGRANT
53232KEY(5b0233670fb9) X
GRANT
53232KEY(6e0212a7298c) XGRANT
53232KEY(97013ece81c8) XGRANT
53231KEY(e7006f987fb0) XGRANT
53213KEY(ad00a33f46b9) XGRANT
53210643560700TAB Sch-MGRANT

any idee?

Thanks

Mateusz

View 1 Replies


ADVERTISEMENT

Sp_who

Jun 4, 2002

Hi,
How can I save result of sp_who into temp table or variables?

Thank you,
Elena

View 1 Replies View Related

Sp_who

Jul 17, 2002

Hi!
I want schedule a job to run sp_who at 5 a.m.
How can I save the sp_who result to check it later.

Thank you,
Elena

View 2 Replies View Related

Sp_lock And Sp_who

Mar 16, 2004

Is there any way to write a line to a table showing who had a lock at a particular time?

We have a need to rollback transactions that persist for too long and cause application errors. what i want to do is to dump the details of the connection into a table for analysis later.

i am thinking along the lines of
SELECT loginname
FROM sp_who
WHERE spid in
(
SELECT spid
FROM sp_lock
)
or something!!!!

any ideas anyone?

View 3 Replies View Related

SP_Lock && SP_Who

Dec 13, 2005

Hi!

I need some examples of SP_Lock & SP_Who.

Best Regards,
Shabber Abbas Rizvi.

View 1 Replies View Related

Sp_who And Citrix

Jul 31, 2006

I have some users on Citrix, running an Access front end with ODBCattached tables.In sp_who. on a normal workstation they'd usually show once or maybemore but all with the same hostname so counting actual number of usersis easy, group on loginame,hostname.When someone connects from a Citrix server, I append the connectionnumber to the workstation name in the connect string so the hostnamelooks something like "CITRIXBOXICA-tcp#256" also for terminal servicesthe hostname will look like "GANDALFRDP-Tcp#8".The problem is that on Citrix the user appears twice with a differenttcp number, the hostname is made up of the name of the citrix/ts server+ the environment variable %SESSIONNAME%, which is surely static for asession?Has anyone seen this in Citrix? Does it ghost old connections? I loogedout and in again just now and saw the sessions look like:CITRIXBOXICA-tcp#199 & CITRIXBOXICA-tcp#254 for my user name thenafter re-logging on I got CITRIXBOXICA-tcp#199 & CITRIXBOXICA-tcp#256so that session 199 has stuck there but wasn't visible in sp_who whilenot logged into the Citrix box.

View 1 Replies View Related

Sp_who And Other Stored Procedure?

Jun 16, 2004

Hi,

Beside sp_who which shows the number of connections to databases,
is there any other sp that shows us the starting time for each connection because the
sp_who does not show the starting of the connection.

Thanks.

View 1 Replies View Related

'sp_who' Status Question...

May 11, 2000

Hi,

I'm using SQL SERVER 7.0. When I do sp_who from Query Analyser to see who has processes running, I get three statuses on the processes, 'runnable', 'background', 'sleeping'.
I can't find anywhere in books, BOL, anywhere that tells me a definition of what each of these statuses mean.
Can anyone help me out?

Thanks,
Darrin

View 1 Replies View Related

Difference Between Sp_who And Sp_who2

Apr 2, 2008

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.

View 4 Replies View Related

Fetch Result Of Sp_WHO

Feb 1, 2007

Hello All,
I need to find out which user are connected to a What database. For this I can Use SP_WHo. But How can i fetch the required fields only dat too in Query Analyzer

Thank you in advance

View 1 Replies View Related

How To Convert Sp_who In A Table/query.

Sep 12, 2007

Hi

I would like to convert sp_who into a table/query. Please help

Thanks

View 3 Replies View Related

How To Convert Sp_who In A Table/query.

Sep 12, 2007

Hi

How to convert a sp_who in into a table/query

Please help



Raj.

View 2 Replies View Related

Change Login-Info For Activity-Monitor And Sp_who

Sep 26, 2007

Hi

we have one 'application'-user in sysusers that makes the connect to SqlServer for all users, for example:

Application Login-User: Thomas
DB-Connect-User: AppUser

With this solution, in Activity-Monitor or with sp_who I don't know, what is the real name of the connected user.
Any possibility to change the login-information after the connect, so that i can see 'Thomas' in Activity-Monitor or with sp_who?

Thanks for your help.
Thomas

View 4 Replies View Related

Spid

Dec 14, 2000

Could someome please tell me what is going on with the following stored procedure:

EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters

View 1 Replies View Related

Spid

Dec 13, 2005

HI!
if i have SPID. then can i get the storedprocedure name ?

Regards,
Shabber Abbas Rizvi.

View 1 Replies View Related

See The SQL For A Spid

Mar 28, 2006

All,

Is there any way to see the SQL for any given spid in SQL server?

I can see "properties" under enterprise mgr - which shows you a snippet of the SQL - or "sp_execute" depending on what you use to get to the DBMS.

Is there a way to take a deeper look?

Thanks,

Isaac

View 2 Replies View Related

Value Of @@SPID

Apr 15, 2004

Hi,

Can I have more than one value for variable @@SPID during a connection?

PS: sorry my english.

Thanks.

View 3 Replies View Related

Spid

May 18, 2004

Hi All,
Sql Server 7.

I have a table cust_detail when ever there is an insert/update/delete in this table it is getting blocked and in turn its blocking other table inv_credit which is doing select statement.

Please suggest me what might be the reason.

Waiting for Reply
TIA
Adil

View 1 Replies View Related

Spid

Jun 19, 2008

Hi to all.

I have a small doubt.
In my server some processs are running slow.
With out the use of profiler hw we can find the spid's that are running slow. is there any procedure for that.

View 5 Replies View Related

SPID In Blk By Is Itself

Dec 29, 2006

I am seeing intermittent blocking problems. The strange part of it is,that the SPID listed in the Blk By column is the same as the SPID thatis being blocked.Has anyone seen this before, or have any idea what this may suggest?

View 1 Replies View Related

Spid Info

May 12, 2000

Hi,
Here i have a question..
Whenever i see my spid in LOCK/PROCESS ID
one command DBCC BUFFERINPUT(MY SPID) always running
and it is showing ''''tempdb.dbo.##lockinfo'''
MODE---- X
Is it pretty normal or something wrong..
i am not running any thing, just simply opened EM..
Pls any suggestions...
thank u

kavira

View 2 Replies View Related

Spid - Blocking

Nov 15, 2000

I know that spid 22 is blocking 2 other users. How do I tell who that user is? Is this just matching the same spid# in the process info in EM and if it doesn't give me an NT name it's probably because they're coming through the application and I can't figure it out?

Thanks

View 2 Replies View Related

Help Killing Spid

Dec 22, 1999

I'm going to try to explain my situation.

I have a spid that I'm not able to kill, this were a select statement from with in access 97 application using a DNS connection.
So even that I reboot the client pc and kill the spid it still shows as active with status RollBack.
We have similar problem before and the only way that it disappear were re - starting Sql.
(system Sql 7.0 with sp1 and Access 97)

Thanks.

View 2 Replies View Related

COM+ Transactions And SPID?

Feb 20, 2003

How do COM+ transactions relate to SQL Server SPID?

I am trying to solve a deadlock issue. Using SQL trace, I find SPID 65 and 71 are trying to acquire resources that are locked by each other. But what I am confuse with is tasks performed by 65 are triggered by different transactions (same for 71). Should different COM+ transaction uses different SPID?

The entire COM+ application uses only one db login.

Please advice.

View 2 Replies View Related

KILL Spid

Feb 19, 1999

I can not stop a process using 'KILL spid'.What else should I try?
process has begun a transaction and does not commit.

View 2 Replies View Related

Killing Spid -2

Apr 15, 2002

I have a problem. I am trying to kill a spid that is blocking updates to a table.
The spid number is '-2'. I am using KILL with UOW and I am getting this error:

Server: Msg 6112, Level 16, State 1, Line 1
Distributed transaction with UOW {FCF8D536-27ED-11D6-9CF2-0002A56BDA54} is in prepared state.
Only Microsoft Distributed Transaction Coordinator can resolve this transaction. KILL command failed.

Users are connecting through MTS server. I am runnnig SQL2000 sp2 +hotfix, on NT4.0.

Has anyone encountered this problem before, and has a solution for it (besides rebooting the MTS and SQL Server)?

Thanks,

Peter

View 1 Replies View Related

Blocked By SPID -1?????

Oct 6, 1998

What is SPID -1? I see nothing listing it in any activity monitor, but it has just recently started blocking some processes.


Thanks,
jim craddock

View 3 Replies View Related

KILL Spid

May 8, 2008

can we just kill spid and drop it without doing any rollback ...?


Thanks,
ServerTeam

View 9 Replies View Related

SPID And CPU Usage

Apr 22, 2008



We are using SQL Server 2000 SP4.

Every week to few weeks we'll have a situation where the cpu will max out on our main db server.

This server is a hoss, dual quad core with 16 gigs of ram. All the system type stuff looks good.

I look into the spids though and I notice a few of them that are very high in cpu.

I believe that I've noticed over time that a certain SPID never releases and starts fresh. Could it be possible that a program is written in a way that it just keeps using it over and over with each new user and it nevers releases that CPU?

I know this is a probably a pretty dumb question and there's alot of details I have to leave out for being brief. Ultimately I have to find out why the CPU is hitting 100 percent and hanging there.

I know it's this program because when I stop the app pool for only that program then the CPU on the SQL box goes back down.

Any help would be appreciated...and feel free to ask more questions for more detail and I'll try to give it.

View 14 Replies View Related

What Is "SIGNAL HANDLER" (SPID 1)?

Aug 8, 2000

What is "SIGNAL HANDLER" (SPID 1)?

And what is SPIDs 1-6 ?



Exec Sp_who:


SPID Status DBName Command
----- ------------------------------ ------ ----------------
1 sleeping master SIGNAL HANDLER
2 BACKGROUND master LOCK MONITOR
3 BACKGROUND master LAZY WRITER
4 sleeping master LOG WRITER
5 sleeping master CHECKPOINT SLEEP
6 BACKGROUND master AWAITING COMMAND

View 1 Replies View Related

Same SPID With Multiple ECID

Aug 16, 2004

Hi Everyone,

I noticed that in my SQL 2000 system, one SPID can have multiple ECID when I run sp_who.

Recently, users get timeout message. In the server performance monitor, I can see the SQLSERVER.exe takes a lot of CPU time when this happens.

Could anyone please explain this to me and provide me the solution for it?

Thanks in advance,

Jason

View 1 Replies View Related

Figuring Out SPID Or A Running Job

Feb 1, 2002

I trying to find a way to find what the SPID of a given job that is running. I am trying to create a script that will give me the SPID the JOB_ID, and JOB_NAME. The problem comes in that if I use sysprocesses I have to pull the JOB_ID from program_name in sysprocesses and convert it into something then join it to sysjobs. Have not been sucessfull in that conversion. Any Ideas

thanks for any & all help

sean

View 1 Replies View Related

Process Being Blocked By SPID -1

May 10, 1999

The following is an extract from sp_who2

SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO
----- ---------- ------- -------- ----- ------- ------- ------- ------
162 sleeping om18682 . -1 STIPROD SELECT 236 120

Has anybody experienced a process being blocked by SPID -1.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved