Excessive Blocking - Runnable Processes
May 23, 2006
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.
View 7 Replies
ADVERTISEMENT
Jul 20, 2005
Hello 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 Related
Apr 16, 2008
I'm running into a blocking problem on my SQL 2000 server. I have a table that is frequently read/written to (inserts, updates, deletes) -- I don't place any explicity locks but I do a SELECT @@Identity after I insert a record to get the Identity value via a sqlCommand.ExecuteScalar.
So my questions:
#1 Is blocking normal? (40-90 blocks consistantly - 350 or so client connections)
#2 Is there any better coding solution to avoid blocks?
#3 I need to get the Identity value after the recorded is added and I thought ExecuteScalar is the fastest and least overhead, put perhaps I'm wrong?
Any suggestions or hints welcome.
Thanks, Rob.
.NET 2.0
View 4 Replies
View Related
May 29, 2001
I have upgraded a MS SQL database from 6.5 to 7.0. The database functioned fine in 6.5, now I have a table that is locking due to a blocking process. If I kill the process all is fine, but am trying to determine what is causing the process to hang. Has anyone experience any similar situations.
View 2 Replies
View Related
Apr 12, 2007
Today I ended up in a situation where I had a process with total six "subthreads" (identified by different execution context) (seen in Activity Monitor). All of these had blocking=1. The server didn't function properly, I don't know the details of these problems, since I was not present at that time. We had to kill the processes. What is the process id 1, "RESOURCE MONITOR" in SQL Server 2005, seen in Activity Monitor? Is it fatal if some processes are blocking RESOURCE MONITOR? How can one end up in such situation, is it normal or a bug somewhere?
The server is a 64-bit Windows server having SQL Server 2005 SP1.
Yesterday I had a CLR stored procedure running on another server. The procedure uses System.Data.SqlClient.SqlConnection to access this server. The procedure started about 11.4.2007 22:22. The procedure created a connection to the SQL Server and created a select that should return 1,5 million rows. During fetching the rows (about after 800 000 rows) the procedure crashes to an error:"".NET Framework execution was aborted by escalation policy because of out of memory. " Naturally the procedure couldn't close the SQL Server connections, since it was forced to end.
The details if the processes as seen from Actívity Monitor (I only have screenshots so I can't copy-paste...):
The main process:
Process id: 69
status: suspended
open transactions: 1
command: SELECT
Application: .NET SqlClient Data Provider
Wait time: 578
Wait type: ASYNC_NETWORK_ID
CPU: 1375
Physical IO: 22
Memory usage: 2
Login time: 11.4.2007 22:22:05
Last batch: 11.4.2007 22:22:05
Blocked by: 0
Blocking: 1
Execution context: 0
Two "subthreads", there are five similar.
Process id: 69
status: suspended
open transactions: 0
command: SELECT
Application: .NET SqlClient Data Provider
Wait time: 35293046
Wait type: CXPACKET
CPU: 4875
Physical IO: 2214
Memory usage: 2
Login time: 11.4.2007 22:22:05
Last batch: 11.4.2007 22:22:05
Blocked by: 0
Blocking: 1
Execution context: 1
Process id: 69
status: suspended
open transactions: 0
command: SELECT
Application: .NET SqlClient Data Provider
Wait time: 35293031
Wait type: CXPACKET
CPU: 4875
Physical IO: 2210
Memory usage: 2
Login time: 11.4.2007 22:22:05
Last batch: 11.4.2007 22:22:05
Blocked by: 0
Blocking: 1
Execution context: 2
The rest three subthreads differ from the above by having different wait time, CPU, physical IO and execution context.
View 12 Replies
View Related
Jun 5, 2007
Last night I received this error
Process ID 152:3 owns resources that are blocking processes on Scheduler 2.
When I did an BCC INPUTBUFFER I found it was sp_MSadd_repl_commands27hp which is doing the insert into MSrepl_commands has any else noticed and issue w/ sp_MSadd_repl_commands27hp blocking itself. At the time I had about 10 million records to move. I was using the default log reader settings so I was batching them in 500 chunk intervals.
I am wondering if any else has had problems like this? I basically see it whenever I move too much data through my replication server.
I found the followoing link http://support.microsoft.com/kb/319892
Sample Scenario
Client 1 connects to SQL Server.
Client 1 runs a Transact-SQL command that starts a transaction and performs data modification.
For example: begin tran
update authors set au_lname = 'test'
Client 1 becomes IDLE, shows up as sleeping, and awaiting a command with an open transaction in the sysprocesses system table.
Clients 2 through 255: Approximately 254 more clients log on to SQL Server and issue a SELECT from the authors table. These clients will all become blocked on the original update.
Client 1 tries to commit the transaction but it becomes queued because all the worker threads are tied up by clients 2 through 255.
I am afraid that I am seeing this more then I would like does anyone know a way to prevent this from happening?
View 1 Replies
View Related
Sep 11, 2007
The following question applies to SQL Server 8.0.2187 (2000 + SP4+916287/914384/898709/915065/915340):
We have now twice had an incident where the same SQL Server has stopped responding. The only workaround is to restart the SQL Service. After this occurs, the log is filled with the following messages:
2007-09-10 16:42:14.29 spid3 Process ID 197:320 owns resources that are blocking processes on Scheduler 1.
2007-09-10 16:42:14.31 spid3 Process ID 74:324 owns resources that are blocking processes on Scheduler 5.
We haven't been able to pinpoint a cause or reporduce the problem on a dev server. I've seen several posts about this issue online but not many answers. Does anyone have any advice on how to troubleshoot this issue?
View 1 Replies
View Related
Apr 13, 1999
In 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
View 2 Replies
View Related
Jun 10, 2004
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 Replies
View Related
Nov 1, 2006
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 Related
May 31, 2001
We are facing lot of problems with Blocking,can any one help us in this matter,The problem is as follows
We have SQL Server 7.0 running on Nt4.0, and three web servers and 5 application servers are accessing SQL server.
Till Yesterday everything was fine,Suddenly today more than 18 processes
were blocked by other(Like chain),First i killed some blocking process,then it was fine,once again it started and continuously some processes are blocked by other,and i found that all blocking process are running from webservers.I ran SQL Profiler to get some information,but no use.
I am not understanding why suddenly it happend,because we have't modified anything.Is there any way to overcome this situation,this is production server.
because of this users are getting slow responce/no responce.
----Here i want to know why it happend?
---How to trace the problem and fix it
Can any one help me please
Thanks
Ananth
View 1 Replies
View Related
Feb 11, 1999
Help !!
I am running a database of 500-600mb 20-30% of which is new data daily (5 day old data being deleted as part of the nightly maintenance) And my nightly maintenance is regularly taking an hour plus.
CheckDB, New Alloc, Catalog, re-indexing and dumps are performed nightly (2am ish) and as the system is in constant use I cannot afford such a long task. I can't use weekly dumps/checkDB as we use transaction log replication and these are dumped every minute. I really need some suggestions on how I can improve matters. The deletion of old data in particular is taking a long time due to the use local variables but is there a faster way to do this :
OPEN tnames_cursor
FETCH NEXT FROM tnames_cursor INTO @connectionid
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
Select @dRent = DeliveredRetention from ControlDB..connectiontable
where ControlDB..connectiontable.Cid = @connectionid
Delete from MyDB..Table where Cid = @cid
and DateDelivered != NULL
and Datediff(hh,MyDB..Table.DateDelivered,getdate()) >= (@dRent*24)
END
FETCH NEXT FROM tnames_cursor INTO @connectionid
END
DEALLOCATE tnames_cursor
GO
These jobs have also started running out of locks and deadlocking on occaision which seems odd as the system has 10000 available (escalating at 2000)
Any Suggestions would be very much appreciated
Damon
View 1 Replies
View Related
Nov 27, 2006
Hi there group.
Could some please point me in the right direction?
We have a database and it's about 28GB in size, recently the SQL server process that runs uses approximately 1.6GB of Memory.
I have tried running SQL profiler to find out which Stored Procedure is causing this but came up unsuccessful.
When restarting SQL the process it run's at about 50MB for about 20sec and then starts climbing up to 1.6GB of memory usage.
Please assist.
View 12 Replies
View Related
Mar 16, 1999
We recently upgraded from SQL 6.5 to SQL 7. I have a few .sql files that were each running around 5 - 8 minutes under 6.5. These same files now each take over 30 minutes to run. Has anybody had problems with their queries taking longer to run under 7.0? These files are quite large and are comprised of 3 - 4 batches with several queries in each batch. If anybody has any thoughts on the cause please let me know.
Thanks in advance.
View 1 Replies
View Related
Jun 25, 2007
Since the other related topic is closed/answered...
The Short version:
SQL is now logging too much info with every package. The volume of the new "User: Diagnostic" event has caused some packages to fail and the command-line exclusion option appears to have no effect on the events logged to the SQL provider. Is this a bug in dtexec or am I using the wrong syntax to exclude log entries? I don't want to modify all of my SSIS packages...
More Info:
SQL SP2 introduced new logging events, most of which appear to get logged by default. So far, none of our packages have used any sort of explicit logging configuration; it's all been set at the command line using a syntax like shown below:
dtexec.exe /FILE "D:SSIS PackagesMyAppVendors.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REP E;Diagnostic /LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"MyDBConnName"
This does appear to correctly limit what gets logged to the console (and thereby the SQL Agent's job step log), but has no effect on what's logged to the database. Normally, I'd use /REP EWDCI, but I was attempting to limit the log entries to Errors only.
I first came across this error when a package failed, but it only logged the following to the console with nothing in sysdtslog90 (while not the "latest/greatest" server, this is a relatively low-utilized quad 2.8ghz xeon ProLiant DL580 G2):
Error: 2007-06-21 06:01:30.45
Code: 0xC0202009
Source: MYPACKAGENAME Log provider "{0C3CBE9B-D828-41C2-98D2-99BA498B314A}"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Connection is busy with results for another command".
End Error
Error: 2007-06-21 06:01:30.46
Code: 0xC0014010
Source: MYPACKAGESTEP Load
Description: The SSIS logging provider "{0C3CBE9B-D828-41C2-98D2-99BA498B314A}" failed with error code 0xC0202009 ((null)). This indicates a logging error attributable to the specified log provider.
End Error
I changed this one package to only log OnError events, but I'd rather not have to change every package to do the same, plus I'd like the ability to easily turn on verbose or any other logging level when needed.
View 1 Replies
View Related
May 22, 2006
have a 3rd party sql 2000 app, mostly bad sql. have lock issues, when monitoring sql locks/req per second, I get normally between 500,000 and 1,000,000 requests. For a 4 way box with 16 gig of memory, what is considered an excessive amounts of locks.
View 3 Replies
View Related
Apr 18, 2000
I have got SQLv6.5 SP5a with SMS1.2 SP4 on seperate Alpha boxes. I have automated the backups so they are scheduled for after hours. SMS gets backed up first and TEMPDB shortly afterwards. However, since a back log in SMS MIFS has happened, the TEMPDB backup displays of 100,000pages backed up. When you back it up on its own, it only shows 170+ pages.
The SMS DB is 600MB in size, the Log is 210MB, Open objects is 5000, and TEMPDB is set 210MB on its own device.
Any ideas
View 1 Replies
View Related
Jul 23, 2005
Hello!I am trying to investigate strange problem with particular storedprocedure. It runs OK for several days and suddenly we start gettingand lotof locks. The reason being [COMPILE] lock placed on this procedure. Asaresult, we have 40-50 other connections waiting, then next connectionusingthis procedure has [COMPILE] lock etc. Client is fully qualifyingstoredprocedure by database/owner name and it doesn't start with sp_. I knowthese are the reasons for [COMPILE] lock being placed. Is theresomethingelse that might trigger this lock? When troubleshooting this issue, Inoticed there was no plan for this procedure in syscacheobjects. Thestoredprocedure is very simple (I know it could be rewritten/optimized butourdeveloper wrote it):CREATE PROCEDURE [dbo].[vsp_mail_select]@user_id int,@folder_id int,@is_read bit = 1, --IF 1, pull everything, else just pull unread mail@start_index int = null, --unused for now, we return everything@total_count int = null output, -- count of all mail in specifiedfolder@unread_count int = null output -- count of unread mail in specifiedfolderASSET NOCOUNT ONselect m1.* from mail m1(nolock) where m1.user_id=@user_id andfolder_id=@folder_id and ((@is_read=0 and is_read=0) or (@is_read=1))orderby date_sent descselect @total_count = count(mail_id) from mail m1(nolock) wherem1.user_id=@user_id and folder_id=@folder_id and ((is_read=0 and@is_read=0)or (@is_read=1))select @unread_count = count(mail_id) from mail m1(nolock) wherem1.user_id=@user_id and folder_id=@folder_id and is_read=0GOI was monitoring server for a couple of day before and I am not surewhythis happens every 3-4 days only!Any help on this matter would be greately appreciated!Thanks,Igor
View 1 Replies
View Related
Aug 9, 2007
Hi all,
I'm trying to get an understanding of a serious problem I have with a large DB in production. This is going to be obvious to someone (everyone probably) <bg>
I have a table which consists of numerous varchars and ints but also a Text type field. This table resides in a SQL 2000 Database. This DB currently has a data file size of 16Gb and a Transaction Log size of 17Gb. When I edit the table and increase the size of a Varchar field from 50 to 100 these files grow to more than double their size!
Why is this happening and how can I prevent this?
TIA
NozFx
View 1 Replies
View Related
Jun 8, 2015
I am getting this massage in error log .
"Database XYZ has more than 1000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files."
I am using sql server 2008r2.
View 5 Replies
View Related
Jul 31, 2007
We are running SQL Server 2000 Enterprise Edition on a 2-node cluster with IIS/ASP.NET front-end hosting 150-200 active connections. There is a SVCHOST process running under LOCAL SERVICE account - hosting the Remote Registry process that is using only 4,200K but is page faulting 200-500 times per second. I realize this process is used for failover, but the page fault seems excessive. Any thoughts on this?
The servers are running Windows Server 2003 with 4 processors and 4gb RAM.
View 1 Replies
View Related
Aug 3, 2007
We have a SQL2000 database (Publisher) replicating inserts and updates across a 10Mb link to a SQL 2005 database (Subscriber). The Publisher has two tables we are interested in, 1 with 50 columns and 1 with 15. Both tables have 6 insert/update triggers that fire when a change is made to update columns on the publisher database.
We have set up a pull transactional replication from the Subscriber to occur against the Publisher every minute. We have limited the subscription/replication configuration to Publsih 6 columns from table 1 and 4 from table 2. Any change occuring on any other columns in the Publisher are of no interest. The SQL 2005 database has a trigger on table 1 and table 2 to insert values into a third table. There are around 7,000 insert/updates on table 1 and 28,000 on table 2 per day. All fields in the tables are text.
We are seeing "excessive" network traffic occuring of approximately 1MB per minute (approx 2GB per 24 hrs). We also see that the Distributor databases are getting very large -- upto around 30GB and growing until they get culled. We have reduced the culling intrval from 72 hrs to 24 hours to reduce the size.
Does anyone have any suggestions as to how this "excessive" network traffic can be minimised and how the distributor database size can be minimised. I think that maybe they are both related?
Thanks,
Geoff
WA POLICE
View 5 Replies
View Related
Sep 25, 2007
We have a large number of clients attempting to replicate two publications on 2005 Express databases (2 publications subscribed to the one subscriber database) with our 2005 Server (9.00.3042.00 SP2 Standard Edition) and experiencing two significant problems:
1) Users experience the following message:
The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the Subscriber for changes not yet sent to the Publisher. You must reinitialize the subscription (without upload).
This problem should not apparently occur with SQL Server 2005 (or 2005 Express) instances with SP2 applied. All clients experiencing this problem have SP2 installed as does our Server and the retention period is 30 days. The subscribers have been replicating well under that.
2) Replications never succeed after appearing to replicate/loop around for hours
This issue is the most critical as we have clients who have been installed and re-installed with new instances of SQL Server 2005 Express, new empty databases (on subscriber before snapshot extraction), and using fresh snapshots (less than an few hours old) which cannot successfully replicate.
Interestingly there is at least 1 instance where several computers are subscribed and successfully replicating the same database as another where replication refuses to succeed.
To test we have taken a republished database from another 2005 Server which is working fine and restored it to the same server as the one holding the database with which we are experiencing problems and subscribed to it. This test worked fine and replication of both publications went through fast and repeatedly without showing any signs of problem.
This indicates that the problem is perhaps data related as it appears localised to that database.
Below are two screenshots which may assist.
Screenshot 1 Shows that on the server side the replication attempts look like they are succeeding despite the fact that the subscriber end does not indicate success. Also the history indicates the the subscription has spent all it's time initialising and not merging any changes.
Screenshot 2 Shows a rogue process which has appears on many of the problem child subscribers. It shows a process running with no end time even though the job indicates failure in the message and even though other replication attempts appear to have succeeded after it. This process stays in the history showing that it is running even when I can find no corresponding process for it.
Can anyone suggest a further course of action/further testing/further information required which may assist?
This is extremely urgent and any assistance would be greatly appreciated!
Thanks in advance!
Scott
View 5 Replies
View Related
Feb 10, 2000
SQL7 SP1 NT4 SP5
Hi.
Very long story and I will not bore you with it. What I need to do is call a stored procedure and from within that stored procedure, initiate other sp and get out before those other sp are finished running.
I know I can execute a job but my volume would be about 1 job per second with a life of 2-5 seconds. So in a very short period of time, I can stack up a large volume of jobs.
Anyone have any better ideas ?
Help Please !@!!
Craig Somberg
csomberg@stageone.com
View 2 Replies
View Related
Sep 20, 2000
Hi,
How to kill process???
I turn off workstation but the process still available in the sysprocesses table and in the Curent activity window.
I can't to kill this (and I can't to restart serever, because the users) .
If anyone know something about this problem.
Thanks
View 2 Replies
View Related
Mar 16, 1999
I have an SQL server with several (100) users connected. When I run a long running process, it severely impacts user performance. The long running process is a store procedure with several cursors.
Are there SQL configuration settings that would reduce the impact of these long running processes on other Users?
View 2 Replies
View Related
Sep 26, 2006
/*
The Following Stored Procedure helps to
Kill All Processes in a Particular DataBase
With Out Current Process
*/
Create Proc Sp_KillAllProcessInDB
@DbName VarChar(100)
as
if db_id(@DbName) = Null
begin
Print 'DataBase dose not Exist'
end
else
Begin
Declare @spId Varchar(30)
DECLARE TmpCursor CURSOR FOR
Select 'Kill ' + convert(Varchar, spid) as spId
from master..SysProcesses
where db_Name(dbID) = @DbName
and spId <> @@SpId
and spId > 50
and dbID <> 0
OPEN TmpCursor
FETCH NEXT FROM TmpCursor
INTO @spId
WHILE @@FETCH_STATUS = 0
BEGIN
Exec (@spId)
FETCH NEXT FROM TmpCursor
INTO @spId
END
CLOSE TmpCursor
DEALLOCATE TmpCursor
end
/* The Above Query Helps TO Change a Database in Single USer Mode Quickly*/
View 5 Replies
View Related
May 8, 2002
I have been needing to disconnect users often, before backing up a database or setting it to restricted users.
Each time, I have to go to Current Activity & kill one process at a time. Is there a way, by which I can kill all processes on a database or force out all coonections to it?
Thanks.
View 4 Replies
View Related
Feb 22, 2006
What is the max number of processes that can be running at the same time?
View 1 Replies
View Related
May 4, 2004
Can you kill processes in SQL Server 2K without stopping SQL Server and restarting it? I am using sp_who2 to get a list of active users and I see some accounts that are logged off but still showing up and I am trying to find a way to Kill these accounts and the processes they are doing.
View 3 Replies
View Related
May 17, 2004
From the system table sysprocesses, how to do in order to extract
the SQL command related to the processes ?
What are system views/tables, scripts used to monitor and obtain more information
about processes, connexions... ?
View 2 Replies
View Related
Apr 1, 2008
Hi All
When i see the result of Sp_who2, i see the status of a process as
"SUSPENDED" for some of the processes like logwriter, checkpoint etc.
and also found the same status for "IF @@TRANCOUNT>0 COMMIT TRAN"
Please let me know what does it mean in the context of SQL 2005
Thanks in advance.
View 8 Replies
View Related
Jan 24, 2008
Hi all,
Can anybody tell me what are the background processes running in sqlserver databases.
regards
Susheel
View 3 Replies
View Related
