Blocked By SPID -1?????
Oct 6, 1998What is SPID -1? I see nothing listing it in any activity monitor, but it has just recently started blocking some processes.
Thanks,
jim craddock
ADVERTISEMENT
Process Being Blocked By SPID -1
May 10, 1999The 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.
Spid Blocked By Itself On SQL 2000 SP4
Jul 31, 2007Hi
On one of my SQL servers (SQL 2000 SP4) i have a problem with spid blocked by itself (spid 54 (Blocked by 54)).
On the server it's running a verry simple UPDATE statement (from the Query Analizer)'. The query needs very long time to finalize (on SP3 it was very fast). I searched some forums regarding this issue and i found that maybe the SP4 can cause this problem.
Do you have any idea or advice?
Thanks
SQL 2012 :: Task Manager SPID Blocked System
Sep 1, 2015We had this issue yesterday, for like an hour. Suddenly, our website started to get slow and get timeout errors. After executing sp_who2 and sp_whoisactive, we found a system process blocking many other processes. Of course, it was impossible for us to kill it.
Under "Status" it said TASK MANAGER, "Login" sa, from sp_who2.
Under "sql_text" it was NULL, "program_name" empty, "open_tran_count" changed like this: 2,3,4,3,4,2,1 until it finished, and "database_name" changed from our database to master at the end, from sp_whoisactive.
Our CPU usage and memory at that moment were almost the same as any other day.
We don't do backups or execute jobs at that particular time. We don't even use "sa" account.
What could have happened? In the event viewer we've found nothing, nor in SQL server logs.
Once it finished everything was back to normal, we want to prevent this from happening again.
Spid
Dec 14, 2000Could someome please tell me what is going on with the following stored procedure:
EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
Spid
Dec 13, 2005HI!
if i have SPID. then can i get the storedprocedure name ?
Regards,
Shabber Abbas Rizvi.
See The SQL For A Spid
Mar 28, 2006All,
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
Value Of @@SPID
Apr 15, 2004Hi,
Can I have more than one value for variable @@SPID during a connection?
PS: sorry my english.
Thanks.
Spid
May 18, 2004Hi 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
Spid
Jun 19, 2008Hi 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.
SPID In Blk By Is Itself
Dec 29, 2006I 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 RelatedSpid Info
May 12, 2000Hi,
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
Spid - Blocking
Nov 15, 2000I 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
Help Killing Spid
Dec 22, 1999I'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.
COM+ Transactions And SPID?
Feb 20, 2003How 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.
KILL Spid
Feb 19, 1999I can not stop a process using 'KILL spid'.What else should I try?
process has begun a transaction and does not commit.
Killing Spid -2
Apr 15, 2002I 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
KILL Spid
May 8, 2008can we just kill spid and drop it without doing any rollback ...?
Thanks,
ServerTeam
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.
Blocked/Blocking
Dec 7, 1999Hello...
Is it normal in SQL Server 6.5 the user who only running the query blocking the other user who try to update/add the records?
note: The query is a complex SQL.
Many Thanks!
Blocked Process
Mar 17, 1999We are using SQL server 6.5 and currently have about 100 users connections at a given
point in time. The application is Visual Basic 5.0 based and it allows users to create
MS Word documents from the application. These documents names are stored in a table which basically
acts as a reference table. Every time a document needs to be attached to a record
this table is called with an insert/update query. This results in an exclusive page/table
lock and ends up into a blocked process. This finally results into a major halt for
all the system users.
Manual killing of these blocked processes frees up the resources and brings things back to normal
although disruptive to the users.
Any clue as to why the blocked processes are not able to free themsevles up ?
Are we missing something in our SQL configuration that will help us with unblocking
these processes ?
Shrink Db Blocked
Feb 27, 2002Hi,
Issuing 'dbcc shrinkfile (log ,truncateonly)'
I get the error message below. Books online doesn't say any more.
Can anyone explain? I am the only user connected to the db at the time, no jobs are executing.
Cannot shrink log file 2 (log) because all logical log files are in use.
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Still Getting Blocked Despite WITH (NOLOCK)
Apr 5, 2006I'm running a heavy SELECT query using WITH (NOLOCK). This still causes other processes trying to INSERT in one of the tables to get blocked.
I thought the locking hint would prevent from blocking other processes?
Your Upgrade Is Blocked
Jun 29, 2006I am getting the following error message when I tried installing the 2005 Developer Edition from the MSDN kit. I previously installed the 2005 Express Edition, and it worked just fine, so I thought that the install failed as it was at a lower version than the Express Edition. I uninstalled the SQLExpress Edition, however, when I try to install SQL Server 2005 again, I get the following message:
Name: Microsoft SQL Server 2005 Tools
Reason: Your upgrade is blocked. For more information about upgrade support, see the "Version and Edition Upgrades" and "Hardware and Software Requirements" topics in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
Build version check:
Your upgrade is blocked because the existing component is a later version than the version you are trying to upgrade to. To modify an existing SQL Server component, go to Add or Remove Programs in Control Panel, select the component you would like to modify, and then click Change/Remove.
Name: Microsoft SQL Server 2005 Tools
Reason: Your upgrade is blocked. For more information about upgrade support, see the "Version and Edition Upgrades" and "Hardware and Software Requirements" topics in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
Build version check:
Your upgrade is blocked because the existing component is a later version than the version you are trying to upgrade to. To modify an existing SQL Server component, go to Add or Remove Programs in Control Panel, select the component you would like to modify, and then click Change/Remove.
I have also installed Visual Studio NET, and I wonder if some component in the Studio app is holding back the SQL Server install. I have uninstalled all SQL components several times already, and I keep on getting this message.
Gregory
Gregory
Mirroring Blocked And DB Got Down!!!
May 4, 2008Hi all,
I have an SQL Server 2005 mirroring config with "High availability". Twice over the past four weeks, the principal server started logging to the event log:
"All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on DBMIRROR_DBM_EVENT" I checked the database activity monitor and indeed there were lots of processes in DBMIRROR_DBM_EVENT.
The database was totally unresponsive to queries (all got timeouts), and no failover was done.
This are the log entries for all day:
05/04/2008 08:40:44,Server,Unknown,All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on DBMIRROR_DBM_EVENT. Process Utilization 0%.
05/04/2008 08:39:44,Server,Unknown,All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on DBMIRROR_DBM_EVENT. Process Utilization 0%.
05/04/2008 08:38:44,Server,Unknown,All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on DBMIRROR_DBM_EVENT. Process Utilization 0%.
05/04/2008 08:37:44,Server,Unknown,All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on DBMIRROR_DBM_EVENT. Process Utilization 0%.
05/04/2008 08:36:44,Server,Unknown,All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on DBMIRROR_DBM_EVENT. Process Utilization 0%.
05/04/2008 08:35:44,Server,Unknown,All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on DBMIRROR_DBM_EVENT. Process Utilization 0%.
05/04/2008 08:33:49,Server,Unknown,The time stamp counter of CPU on scheduler id 2 is not synchronized with other CPUs.
05/04/2008 03:24:02,Backup,Unknown,Log was backed up. Database: TeleCable<c/> creation date(time): 2007/08/24(13:38:00)<c/> first LSN: 205089:18926:1<c/> last LSN: 209688:18463:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'\face01f$logbackup'}). This is an informational message only. No user action is required.
05/04/2008 00:00:14,spid24s,Unknown,This instance of SQL Server has been using a process ID of 1700 since 4/9/2008 3:18:54 AM (local) 4/9/2008 1:18:54 AM (UTC). This is an informational message only; no user action is required.
After that, the same entry once per minute.
I restarted the database and everything came to normal.
As you can imagine, the problem is really serious.
Should I forget about mirroring? Anyone has experienced something similar?
Thanks in advance and best regards.
What Is "SIGNAL HANDLER" (SPID 1)?
Aug 8, 2000What 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
Same SPID With Multiple ECID
Aug 16, 2004Hi 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
Figuring Out SPID Or A Running Job
Feb 1, 2002I 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
SQL Server SPID Connections
Nov 15, 2004Hello -
I have a Foxpro app with a SQL Server 7 backend. The Idle Connect setting on VFP is set to indefinite. I'm having some connectivity issues where the SPID for the client seems to disappear from the active SQL processes on the server when the client sits idle for a while (10 minutes or so). On some workstations, resuming activity at the client results in the SPIDs reappearing
as if they were always there with no problem. On others, the connection is not reestablished and the client gives a 1526 (VFP ODBC) error.
Does anybody know why this might be? Are the SPID connections cached at both sides and if so, how?
Thanks.
Steve
SA User Logging Off And On Again W/ Same Spid
Feb 27, 2006Hey all,
I've got a third party app running atop my sql server instance. When auditing logons in profiler, I see that the user (sa) logs off, and then immediately logs back on. Interestingly, the app logs back on with this user again, and the instance gives it the same spid as it previously had.
Can anyone explain this behaviour, or point me to a place where I can investigate ?
Thanks,
-Kilka
Spid Network Library
Apr 7, 2006Hey guys,
I'm wondering if it's possible to see which library which a spid is using? For example, is the spid coming from a jdbc connection, or an odbc connection?
Thanks,
-Kilka
Unable To Kill SPID
May 12, 2004Hi all,
Greetings,
I have a SPID 10 which running a query which is below is not getting killed
and this SPID is blocking other SPID's. Is there any way to kill it pls help me
"select A.*
from ARO A where A.InvoiceNumber in
(select B.InvoiceNumber from AR1 B
where B.Division='20'
/*UNION
Select C.SOApplytoInvoiceNumber from AR1 C
where C.Division='20'*/
UNION
Selec"
Waiting for reply
TIA
ADIL
Identifiying Procedures From SPID
May 11, 2007Hi All,
I got problems with some procedure whic are blocking one of my databases. Biggest problem is I don't which those procedures are.
Last time it happened I ran sp_who2 active. I was able to identify two suspended blocked processes, one (UPDATE - SPID:58) blocked by the other one (SELECT - SPID: 68), the other one blocked by a runnable one (UNKNOWN TOKEN - SPID: 70).
For all of them I got SQLAgent - TSQL JobStep (Job [0xDA9A9D92A78C0E42A37EE96C7BEBB073] : Step [1]) so it's pretty obvious they are executed by SQL Agent Jobs.
I am trying now to identify the jobs from the keys 0xDA9A9D92A78C0E42A37EE96C7BEBB073 but no idea how to do it.
I was looking at the SPIDs but I discovered they can change, so no use in that.
Anyone of you guys can help?
Cheers and thanks in advance!
Giovanni