To Get All The SPIDs
Nov 13, 2007is there any way to get all the SPID's running a particular Stored Proc?
Thx
Venu
ADVERTISEMENT
Hanging Spids
Feb 22, 2001We have just combined 3 sites into one server.
Two of the sites are serving fine, however one site has spids
that won't disconnect. By the end of the day there are over 600 spids
from that one site. At first we thought it was due to the ASP pages
had a db connect, but nothing that closed that db connection. But after
they modified the pages, we are still having the same problem. Checked
the webserver and they are identical to the other webservers.
Any ideas???
Let Sleeping SPIDS Lie
Jan 24, 2008A developer came over and asked me a couple of things
1. Can C# not cleanup SPIDs and just open new ones lieing around
2. Do a large number of spids, even if they are sleeping, cause performance issues?
3. What's the Max # of spids sql server can handle?
BOL ain't too helpful right now
Linguring SPIDS
Jul 23, 2005SQL Server 2000Just curious - was wondering why some SPIDS are left hanging out therefor up to several weeks. There are no errors or anything. It looks likenormal processing.Thanks,Craig
View 1 Replies View RelatedMultiple User SPIDs Which Won't Go Away??
May 22, 2001I have an issue with a server where some users are recieving error messages along the lines of "There are currently 250 connected users etc....". the result of which is that the users sometimes can't connect. The server properties have Current Users set to 250 - I know that I could increase this figure but that doesnt't appear to be the problem.
It would seem that connections appear to stay connected even though the user has finished any server/client transactions. Also there seems to be a huge number of SPIDs even when there are only a handfull of users. Most of the 'Process Details' for these user threads show the following: "IF @@TRANCOUNT > 0 COMMIT TRAN".
What is going on and why are these Process IDs hanging around, sometimes the only option is to reboot the client PCs since trying to kill the processes just makes them reappear a few seconds later?
It could be the 32-bit client application causing it, but these problems are only on one server out of half a dozen.
Any ideas?
Thanks, Derek.
SQL Server 2000 Spids Blocking Themselves
Oct 19, 2005Hey guys,
I've recently noticed some strange behaviour with sql server 2000 spid's blocking themselves. The spid will appear to be blocked for short periods of time, and then the block will disappear. I'm not sure how this could occur. It started appearing around the same time as I applied SP4.
If anyone could provide any insight into this, it would be greatly appreciated.
Cheers,
-Kilka
Merge Replication And Spids Left Behind
Jul 20, 2005We are using Merge replication with clients from remote offices (SQL2000, sp3). Recently, I have had a problem with users who arereplicating, and they shut down their laptops. The connection neverdies, and I end up with major blocking issues related to the"orphaned" spid. The tables that are blocked are used to filter dataon each client. Since the orphaned spid is blocking, backups will runforever, and have to be killed, and a SQL management job thatinserts/updates data in these tables has to be killed.If I kill the spid, it shows a rollback at 0% and the status neverchanges. The user has disconnected, and there is really nothing toroll back. How can I get rid of this spid with out restarting SQLserver, or rebooting my server?Any help would be greatly appreciated.Thanks,Amy M
View 1 Replies View RelatedCan Multi-CPU Spawn Several SPIDs From One SP Execution?
Jan 24, 2008Hi,
The myComplexProc stored proc does the following in sequence:
1. Call a UDF
2. Call another Stored Proc
3. Make a SELECT with 4 joins
4. Make another SELECT 4 joins + aggregate.
Question1: if myComplexProc is executed on 4-CPUs SQL2K Server, does it start and complete with a single SPID?
Question2: Is SQL2K smart enough to do some sort of parallelism behind the scene? If yes, can it spawn some extra SPIDs? How does it handle lock or sequencing? Because in the hypothetic scenario above Step N+1 re-uses the results of Step N so it has to wait for Step N to complete.
Thanks very much in advance for any help.
SQL Server Phantom SPIDs Causes Locking And Delays
Nov 10, 2003A fellow developer of mine has created a ASP.Net application that executes some fairly complex stored procedures. This application is for all intensive purposes a queue and 3 or 4 people work on processing items in a FIFO type environment. There is logic in the Stored procedures to make sure each worker get a unique entry using a series of query hints.
Well what we are seeing is if these works are moving at a rapid pace and we execute an sp_who2 on the sql server there are entries that that seems to be hanging there and REMAINING there even after a browser is closed or the disposed method has been called on the connection object. Has anyone else experienced something similar to this with an ASP.Net application used by mutiple people?
My inclination is to blame the design of the application, but before I do that and step on my co-workers toes I thought I would throw this out to the group.
Thanks in advance for your input.
Performance Of Views Accessed By Multiple SPIDs?
Jul 23, 2005I wonder if anyone has any hard fact based pro or contra, especially onperformance, about having views as opposed to tables when the object isbeing accessed for read only by multiple sessions/users/spids. I amparticularly concerned about the multiple instantiations of the view.Relevant thoughts on this are much appreciated.Thanks,Caveman
View 1 Replies View RelatedCursor In Sproc To Kill Spids Loops Forever
Mar 20, 2000I need a stored proc to kill spids, but the following sproc loops infinitely with the same [correct] spid being printed out. What am I doing wrong?
The select statement, when I execute it via the query grid, returns the correct and finite number of spids.
Any help greatly appreciated.
Judith
CREATE PROCEDURE rasp_KillDBProcess
@dbname varchar(128)
AS
declare @KillSpid smallint
declare @SQL varchar(1000)
--
declare DBCursor cursor Forward_only for SELECT distinct l.spid
FROM master.dbo.syslocks l INNER JOIN
master.dbo.sysdatabases d ON l.dbid = d.dbid
WHERE (d.name = N'coj_pcisdata')
open DBCursor
--
Fetch next from DBCursor into @Killspid
--
While (@@Fetch_status <> -1)
Begin
If (@@Fetch_status <> -2)
begin
print 'spid = ' + cast(@killspid as varchar(12))
--exec ('kill ' + @killspid)
end
--
end
Fetch next from DBCursor into @Killspid
--
close dbcursor
deallocate dbcursor
print 'end'
return
Disconnect A User Whoz Locking The DB With Diff SPIDs
Jun 1, 2004I have issued a simple insert statement on table and the statement could not insert a single row into the table.When i check the locks on the tables i found that almost 5000(five thousand) locks on the table for a single user with different SPID(s).
The user is and sql server user(not a windows user) and used to connect to the application.I wanted to disconnect the user from the DB so that all the locks will be freed.But i dunno how to disconnect a user from the DB. I know that i can issue KILL command to Terminate a user process based on the system process ID,but here the same user has nearly 1000 SPIDs.I thought that it would be very big job to kill each and every process and restared the sql server.but i guess its not the correct process to do.
how can i dosconnect a specific user from the DB.
Thanks.
SQL Server Admin 2014 :: MDW Data Collector - Large Number Of SPIDs
Nov 6, 2015I've installed the MDW (Mangement Data Warehouse) database on our central monitoring SQL Server. I've then added a number of servers to be monitored. The data is collected on the servers that are being monitored and uploaded to the central MDW Monitoring server.
On the servers that are being monitored, I'm seeing a large number (over 1000) of SPIDs being generated by 'SQL Server Data Collector'.
Is this normal behaviour? I've seen more blocking as a result of this.
Is there any way to reduce the number of SPIDs generated?