SQL Server Phantom SPIDs Causes Locking And Delays
Nov 10, 2003
A 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.
I 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.
Create a linked server to another box. exec sp_serveroption 'RemoteServer', 'connect timeout', '1' exec sp_serveroption 'RemoteServer', 'query timeout', '1'
Pull the network cable from the remote box.
SELECT * from RemoteServer.Database.dbo.Table1
Why does it take almost exactly 21 seconds, every time, before it fails? How can I get it to fail immediately?
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.
I'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?
We have been having performance issues with a high use webservice that is making ado calls. Using past logs, I narrowed down the bottlenecks to delays that always appear to occur around some ADO call.
When I reproduce the same stored procedure calls in Query Analyzer I get a fast response.
I checked and made sure that the sqlConnection is being closed after use.
I feel like I've hit a brick wall. What could be causing these sporadic delays to calls to SQL Server?
I've noticed lately that my queries through ADO/VB are taking alot longer to process at certain times. The query and the result information never changes, only that at certain times the query takes alot longer than usual. I thought that I possibly need more licenses, or it might be network traffic. I currently use MS SQL Server 2000 small business Ed(5 Cals).
Has anyone any information about performance problems due to licensing issues?
Appointments - There are 3 appointments in this table, which includes a 'date' field Notes - There are 2 notes in this table, which includes an 'appointment' field to relate the notes to their respective appointments and a 'new' field which shows that they have been read (or not).
The draft SQL below is designed to return all PAST appointments that have either unread notes (where new=1) or no notes at all.
SELECT appt.ref FROM (SELECT DISTINCT ref FROM Appointments WHERE DATEDIFF(day, date, '15 JUN 2004')>0) appt, Notes note WHERE (note.appointment=appt.ref AND note.new=1) OR NOT EXISTS(SELECT 1 FROM Notes note2 WHERE note2.appointment=17)
There are NO NOTES with '17' in the appointment field and NO NOTES with new equal to 1, so the SQL should return 3 results. I've used 17 just to test it.
For some reason, the SQL returns 6 results, even though there are only 3 appointments in the database.
I have a package that when I open it up, gives me this error:
Error 1 Error loading myPackage.dtsx: The connection "{AEDA784A-4076-4661-BC99-B4D819E0C21B}" is not found. This error is thrown by Connections collection when the specific connection element is not found.
However, I don't know what this error is possibly referrring to. All of my tasks, where needed, have the appropriate ole db connection and are NOT pointing to this non-existent connection. I deleted all connections except the one I use.
I addition, none of my tasks are showing ANY errors whatsover and package executes perfectly.
1)I usually connect to my remote server via TS using a specific login (it belong to Admins) and through that user I open my SSIS projects (30 packages). Now, I have a new user and the same operation take a long time and I don't know why (same permissions) the reasons for this big delay.
2)When I'm gonna to press "Delete all Breakpoints" from Debug menu BIDS is closed. Previously appears a Microsoft dialog (Microsoft has encountered an error...) asking if you inform or not of these errors.
How do you avoid phantom inserts? Can you recommend a strategy (or other posts, articles) to avoid "phantom inserts" with the default SQL Server isolation level ("Read Committed").
I am seeing something strange. I have a data file that has 6 rows that looks like:
ABC, "OPENING BALANCE", 1234, etc
ABC, "CLOSING BALANCE", 1235, etc
ABC, garbage data, etc
XYZ, "OPENING BALANCE", 1234, etc
XYZ, "CLOSING BALANCE", 1235, etc
[][] -- weird box things that shows up in my flat file conn mgr
I have a script transformation that reads the incoming rows as a single line, then checks for the value of the row.line, whether it's "OPENING BALANCE", or "CLOSING BALANCE". It ignores all other lines.
I even added a message box that pops up when it finds "OPENING BALANCE" or "CLOSING BALANCE". It only pops up 4 times, like it should.
However, when I check the database, it has 6 rows! The 4 good rows are there, and 2 garbage rows with a bunch of NULLS in them.
I really don't understand how this is happening. Please, any ideas.
We are running two NT 2000 servers with two synchronized databases (DB1 as publisher, DB2 as subscriber), MS SQL 7, and IIS (ASP). Synchronization is done by immediately transactional replication. We have a radware switch ( www.radware.com ) between these two servers for load balancing and redundancy issues. The problem is folowing - when I open two windows (connections to database DB1 - publisher) and run the same SQL select command I get two different recordsets (differency is 50% or more in count record rows). Any ideas how to solve this problem?
I think probably is this problem somehow tied with second: error message for stored procedures running from ASP: -2147467259, [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'sa' '. SQL : exec business_update @BusPrimId='1230138980', @business_name='Software-test', @Contact_Name='Brian'
We 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???
SQL 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
I 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.
We 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
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.
I 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
I 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'
I have a table X with 61390 rows on it. IT is a crucial table which is being read and updated constantly. If I create a clustered index on the Primary Key (identity column) and run UPDATE x SET c1 = 1234 where PKCOL = 4321 the best lock I can get is a table Lock. If I create a compound non-clustered index on C1 and PKCOL the best lock I can get is a Page Lock. PAge Lock is OK but as this table is in High demand I want a row lock so others can carry on referenceing other parts of the table. WHy does it take a page lock and not a Row lock?
We are becoming increasingly more involved with SQL Server 7.0 development. Can someone show me a good example of ROW LOCKING with exclusive and share modes? If I am starting a transaction with a SELECT and later an UPDATE, what is the best way to handle locking?
is there a way of locking db on server. i am doing a overtime and pay system but there is a few members of IT staff that are very nosey and this is senstive information and i dont want them looking at the tables on the server can these be locked for only me to view
I'm trying to determine if a row in a table is locked. First, I issue a lock on some rows. Then, I query the syslockinfo table to see which rows are locked. It tells me which rows are locked, but it returns a hashed resource value that, I guess, is somehow related to the key. How can I un-hash the value? Or, how can I hash my own key so that I can compare it with the hashed value? I have tried using the HashBytes function, but I don't know what is actually being hashed when the row is locked.
For example:
begin transaction
select * from test with (rowlock, updlock)
select * from master..syslockinfo
where rsc_objid = (select id from sysobjects where name = 'test') and req_mode = 4
I have a setting where two Web Servers access the same SQL Server database. To gaurantee consistent data writes and reads I need to lock part of the database in special conditions.
I know exactly when I need particular tables of the database to be locked, but I am in doubt of how to do it. Does SQL Server 2005 have any mechanism for providing locks gauranteed to free the lock if one of the web servers crash - or if SQL Server itself crashes?
Currently I am working on asp.net Intranet system.I need some suggestion on records locking for Sqlserver & ASP.net. I want to prevent two user to open the same record for modify.Means if some one has open one record (id=xxxxx) for modify then others should not be able to open same same record (id=xxxxx) for modify .otherwise if both user will open/update same record then information in database will not be updated correctly. Any idea how we can do the record locking so that not two users can open same record(id=xxxxx) for modify.
In the application I'm working on, I need to be able to use different locks on my records during certain transactions. I need to know how to do this programmatically from within VB.net. If someone can show me an example of how to do this or show me where I could find some, I'd appreciate that.
Hi All,According to my observation using SP_WHO2 in my database, some INSERTstatements are getting blocked by SELECT statements. Though theblockingSELECT statement is having ReadPast hint, i think, it will only readpastlocked resources but will not guarantee the select statement itself notblocking other statements(in my case Insert). According to my knowledgeandinformation, select statements take shared locks but why then thesestatements are blocking insert statements?Can there be any other solution other than using NoLock hint as thishintmake sure no shared locks are issued but it can read uncommited data.Need help urgently.ThanksRitesh