Blocking Processes

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


ADVERTISEMENT

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 View Related

Processes Blocking RESOURCE MONITOR, Normal Behaviour?

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

Process ID 152:3 Owns Resources That Are Blocking Processes On Scheduler 2.

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

SQL Server Down: Process ID 121:406 Owns Resources That Are Blocking Processes On Scheduler 4.

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

Processes Were Blocked By Other Processes

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

Async Processes - Help !!

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

KILL Processes, Help To Do This!!!

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

SQL Performance And Processes

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

Kill All Processes In A Particular DB

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

Kill Processes

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

SQL Server Processes

Feb 22, 2006

What is the max number of processes that can be running at the same time?

View 1 Replies View Related

Kill Processes

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

Connexion, Processes ...

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

SUSPENDED Processes

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

Background Processes

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

DAO &> SQL Server Processes

Oct 24, 2005

I need to know exactly what VBA lines Create and Destroy SQL ServerProcesses - i.e. those visible in Enterprise Manager under Management[color=blue]> Current Activity > Process Info.[/color]Why?I am experiencing strange behaviour with Processes that are createdwhen I create a DAO Database Object with the following line:Set m_ResDatabase = DBEngine.Workspaces(0).OpenDatabase(strDSN, False,False, strODBC)This creates the process as expected.However the following lines don't always close the ensuing Process:If Not m_ResRecordSet Is Nothing Thenm_ResRecordSet.CloseSet m_ResRecordSet = NothingEnd IfIf Not m_ResDatabase Is Nothing Thenm_ResDatabase.CloseSet m_ResDatabase = NothingEnd IfIf Not m_ResWorkspace Is Nothing Thenm_ResWorkspace.CloseSet m_ResWorkspace = NothingEnd IfIt seems as if SQL Server keeps hold of the first two Processes andthen will release any subsequent ones.Can anyone shed any light in this - or any good web pages where Imight find some answers?Regards Chris

View 2 Replies View Related

Sleeping Processes

Mar 8, 2007

Hi, just a quick problem/question. Why do processes in the middle of the package goto sleep? Is there a method of making them go?

View 2 Replies View Related

HELP?? Multiple Processes

May 31, 2007

I have a foreach loop which contains a call to an Execute process task. I want the Execute process task to run once for each element in my foreach, but I need to run as many Execute process tasks as my server can handle. What is the best way to do this? Any help would be greatly appreciated.

View 7 Replies View Related

No Processes On The End Of PIPE

Mar 3, 2006

I had to move my developed software onto another computer in order to demo this. The new computer has IIS installed as well as VS2005 and SQL Server 2005. When I try to execute I get this error.



A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

I have worked on this problem for two days now and have to demo it first thing Monday. Any help would be appreciated.

View 9 Replies View Related

How To Increase The Maximum No. Of DB Processes?

Sep 4, 1998

I am receiving an error from my ODBC driver “Maximum number of DBPROCESSes already allocated.”

I confirmed that there are 25 connections and that this is the default. This is caused by error message 10029, SQLEDBPS, when the maximum number of simultaneously open DBPROCESS structures exceeds the current setting. I would like to increase this maximum.

I have found only two ways to do this. One is using dbsetmaxprocs using C and the other is using SqlSetMaxProcs using Visual Basic. My problem is that I am interfacing to SQL Server using a third party tool that is doing the lower level programming.

Is there some way that I can increase the maximum number of DB processes for all databases that are part of the SQL Server 7 environment, or can I set this value using a program that is called from a stored procedure?

Any ideas in this area will be greatly appreciated.

View 1 Replies View Related

Automated Killing Of Processes

Aug 30, 1999

I wish to select processes from sysprocess that are SLEEPING and more than a certain time old (say 10 minutes) so that I may KILL them. I can get the query to do the select, but how do I KILL the process? I have tried selecting the SPID into a local variable and then trying KILL @var_name, but I get "Incorrect syntax near '@var_name'".

I have tried all of the resources that I can find, but without success. Is this possible? If so, how do I go about doing it?

Thanks in advance.


Matthew

View 3 Replies View Related

Defing Business Processes

Feb 12, 2007

Sorry, didn't quite know where this should go.
I'm reading Kimball Group's "Microsoft Data Warehouse Toolkit"
Has anyone got any experience in defining business processes? I'm struggling a little to look at my employer's recruitment busines and work out exactly what the real processes are.
Or maybe one of you has read something somewhere about this specific skill?

------------------------
Me: What do you want to know from your data warehouse?
Client: Err...Emm...Everything
Me: OK, that's great. That's all I need to know. I'll see you when it's done.

View 2 Replies View Related

Captures All Server And Processes

Feb 28, 2008

Hi ,

I want to capture all Processes,Applications and Servers (name) which is accessing(linking) to my server. Is it Possible without using Profiler?

View 8 Replies View Related

Limiting Active Processes?

Jul 23, 2005

We currently have a routine that "forks" out (to use the unix term)TSQLcommands to run asynchronously via SQL Agent jobs. Each TSQL commandgets its own Job, and the job starts immediately after creation.Sometimes we can have too many of these jobs running at the same, andthe box crawls to a slow speed until the jobs finish up.Is there a way we can limit the number of active jobs running under theSQL Agent at one time? Or is there away to limit the number of active(runnable) processes on SQL Server, in general?

View 2 Replies View Related

How To Document ETL Packages/processes

Apr 2, 2008

Is there a common way to document ETL (packages / data flows)? I looked for documentation templates in the internet but didn't find anything which was useful.

Thanks,
Kenan

View 1 Replies View Related

How Xml Could Help Us With Our ETL Processes On Daily-basis?

May 26, 2006

Hi all of you,

I'm just a newbie with XML. Now we're moving all our DTS to SSIS packages. Most of them are simply processes that takes a plain file and loading these data into Sql table or in inverse way. So that I wonder what role will play XML here.

I mean, are there big differences between store data as XML format and nvarchar?

That sort of stuff.

Thanks in advance for any input,

View 5 Replies View Related

How Can I Kill Sleeping Processes In SQL Server?

Sep 14, 2006

Dear, Our ASP.NET scripts send SQL statements (as inline SQL or SP) to process the requested job. After the job execution, the process ID stays in the server and waits for next command with sleeping status.Since this process does not go away, next job adds another process and eventually, the server is overloaded with these processes and dies. How can I kill this sleeping processes?Regards,Echo

View 6 Replies View Related

Handling Long Running SQL Processes

Feb 10, 2008

Hello,

I am having some difficulty with data-driven processes in an internal web application using ASP.NET framework 2.0 with SQL Server 2000.

I have a facility where the user enters search criteria to retrieve a list with the idea being that when they click a button the list set is migrated to a table as a batch. The SQL Server processes to perform the database operations can take up to 5min because of volume of data and relational complexity, which is fine.

The only problem is that I'm having trouble managing the SQL Server process if the user closes the browser or go to a new URL etc. If this happens on the client side it's like the HTTP request is terminated but the SQL statement left on the SQL Server still running, eventually it runs to completion but I would prefer the SQL process to also be abandoned and rolled back.

Any idea what is happening here and how it can be handled?

Thanks for any help.

A.C

View 2 Replies View Related

Mutiple Processes/connections Problem

Dec 27, 2004

Hi All,

I'm relatively new to ASP.NET coming from ASP. I've created a new, relatively simple ASP.NET application using WebMatrix with various controls and pages accessing an SQL Server database.

3 people are trying to use this app. After a short amount of time, the server starts kicking back an error message that it can not open a connection. I go in with Enterprise Manager to the SQL Server and I can see a long list of processes there almost as if each page is opening a connection and not closing. I'm explicitly closing all connections in my code where I open them for use by a data reader. What about data grid controls. Do they leave connections open? How about the drag and drop insert/update/delete functions. Unless I'm mistaken, this should close the connection when the function completes, shouldn't it:

Try
rowsAffected = dbCommand.ExecuteNonQuery
Finally
dbConnection.Close
End Try

I originally did this app connecting to an Access database but had the same problem. I figured it was due to the known connection issues with Access via ADO so I upsized to SQL Server and over time, get this same problem.

Any help would be appreciated!
Thanks,
Larry

View 2 Replies View Related

Killing Processes Prior To Restore

May 13, 2002

I have a scheduled job that will do a database restore at given time every day. Sometimes I run into a situation where some people leave themselves logged on to the database, which prevents the job from running.

Is there a way that I can set up my job to include killing any open processes against the database that I'm restoring prior to the restore being done?

View 3 Replies View Related

Need Script To Kill All Processes On A Database

Aug 2, 2007

I restore a backup every week. It serves a couple of purposes. One, to verify the backup and secondly, to give us a current testing/training environment. As things evolve, it would be nice to script this and then schedule it as a job.

I can write the restore statement, but it fails if any sessions are open on the db. I thought about using sp_who and the the kill statement. But I was having trouble using the recordset returned by sp_who.

Now I'm thinking the sysprocess table is a better place to look. Does anybody have some experience in this area and can point me in the right direction?

While I'm going down this path, does anybody have a script to look at the master database and dynamically manage their backup strategy? That would be super cool?!?!

Thanks,

Alex8675

View 4 Replies View Related

SSIS Processes All Files In A FTP Folder

Jan 2, 2007

Hello everyone - wonder if you can help.

How do you process all files in an FTP directory - similar to the for each loop for files.

I need to be able to download each file and then move it to an archive folder on the ftp site.
Might also want to do some things in between.

I have a feeling it means getting the directory listing into a recordset or variable then enumerating that.

Sounds like a common requirement and would be quite easy by other means. Can't help feeling that SSIS has built in tasks to do it.

View 2 Replies View Related







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