473,883 Members | 1,667 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Killing the process automatically

Hi everybody,

We have a very large database and high transaction volume. Time to time
these transactions are locking each other and decrease the performance
of the database. Is there any way that I can automate the killing
process when blocking and deadlock time is exceeded in certain time
elipsade? Can somebody help me on this please?

Regards
asa.

Apr 12 '06 #1
6 4650
laststubborn wrote:
Hi everybody,

We have a very large database and high transaction volume. Time to time
these transactions are locking each other and decrease the performance
of the database. Is there any way that I can automate the killing
process when blocking and deadlock time is exceeded in certain time
elipsade? Can somebody help me on this please?


If SQL Server detects a deadlock it will kill one of the two involved TX
automatically. But you should really change your app to prevent these
deadlocks.

You probably cannot do much about normal locking as this is expected
behavior other than probably optimizing your SQL to make it faster.

HTH

robert
Apr 12 '06 #2
Is it possible to change this deadlock killing time? for instance lets
say instead of 5 min change it to 2 min??

Thanks

Apr 12 '06 #3
laststubborn wrote:
Is it possible to change this deadlock killing time? for instance lets
say instead of 5 min change it to 2 min??


read the docs (BOL)

Customizing the Lock Time-out
When Microsoft® SQL Server™ 2000 cannot grant a lock to a transaction on
a resource because another transaction already owns a conflicting lock
on that resource, the first transaction becomes blocked waiting on that
resource. If this causes a deadlock, SQL Server terminates one of the
participating transactions (with no time-out involved). If there is no
deadlock, the transaction requesting the lock is blocked until the other
transaction releases the lock. By default, there is no mandatory
time-out period, and no way to test if a resource is locked before
locking it, except to attempt to access the data (and potentially get
blocked indefinitely).
robert
Apr 12 '06 #4
laststubborn (ar*********@gm ail.com) writes:
Is it possible to change this deadlock killing time? for instance lets
say instead of 5 min change it to 2 min??


A deadlock does not take five minutes to sort out. It seems that you
have a misconception of what a deadlock is. A deadlock is when two
processes are blocking each other, so none of them can continue. This
is something that SQL Server detects automatically. It usually takes a
couple of seconds.

But one long-running process can block other processes (than in their
turn can block other processes etc) without any deadlock to occur.

I would advice against any automatic killing, as supposedly some processes
are more important than others. It's better to analyse what those blockers
are up to, and if the queries can be improved, or indexes added to
speed up these queries.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 12 '06 #5
Not sure if this could be relevant but perhaps add WITH(NOLOCK) on your
queries.. With this, no locks would actually happen.

Apr 13 '06 #6

"D0MZE" <do******@gmail .com> wrote in message
news:11******** **************@ i40g2000cwc.goo glegroups.com.. .
Not sure if this could be relevant but perhaps add WITH(NOLOCK) on your
queries.. With this, no locks would actually happen.
Not quite.

For a select it basically means to ignore locks on rows.

This can mean you can get phantom rows, not get rows you should etc. i.e.
you'll get an inconsistent view of the table at the time.

This MAY be acceptable in some circumstances, but in others would be
completely verbotin. (imagine an ATM that did a look up on cache available
with a (NOLOCK) while your bank is deleting your last check. You'd falsely
be told you have more money available than you actually do and could
overdraw the account.)


Apr 13 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
9901
by: Jacek Pop³awski | last post by:
Hello. I am going to write python script which will read python command from socket, run it and return some values back to socket. My problem is, that I need some timeout. I need to say for example: os.system("someapplication.exe") and kill it, if it waits longer than let's say 100 seconds
6
1673
by: Dakkar | last post by:
How can i control my program when someone kill it's process from the taskmanager my dispose script is working well if i close the program manually but if i kill it from taskmanager its not working how can i fix it my dispose script is like this protected override void Dispose(bool Disposing) { if (Disposing)
0
1155
by: Shruti A via .NET 247 | last post by:
hello group I have recently started working on .Net platform. I am facing one problem in killing and starting process from my aspx page. I am able to kill and start the same process from vb.net but failed to do it from asp.net the exception that i am getting is :: "Couldn't get process information from remote machine." although i am doing it from the same machine from where i did it successfully using vb.net. don't know the route...
3
2014
by: kunal.kewalramani | last post by:
I'm opening an Excel file using ASP.NET, but Excel process remains it is not killed, I tried killing it by using Quit() that is also not working, if anybody have any solution for this please help me out. ********************************************************************** Sent via Fuzzy Software @ http://www.fuzzysoftware.com/ Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
10
8510
by: shiry | last post by:
Hi, I need to do some important cleanup before my console application exists. I used the console ctrl event. This is working well and it fires for all cases, including the CTRL_CLOSE_EVENT (if I close the application from the X botton). But on the contrary to what the MSDN indicates (ms-help://MS.VSCC.2003/MS.MSDNQTR.2003OCT.1033/dllproc/base/handlerroutine.htm), ending my process from the task manager did not fire the CTRL_CLOSE_EVENT....
8
4745
by: Rain | last post by:
Does anyone know how i can kill a process using C#? im developing an asp.net web application using C# and the asp.net process doesnt die even if the application has already exited. So i need to kill the process someone.. does anyone know the code for this? thank you so much in advance!!!!
2
6241
by: tony.newsgrps | last post by:
Hi there, I'm trying to understand the impact of killing a process that owns a system mutex (used to ensure there is only 1 instance of my program running) Here is my code pretty much: try { mutex=new System.Threading.Mutex( true, mutexName, out createdNew)
4
4612
by: Thomas Nelson | last post by:
Hi, I'd like to start a program, run it for a while, then terminate it. I can do this on linux, but I'm new to working with windows. Here's my script: from subprocess import Popen from time import sleep import win32api war3game = Popen() sleep(30) print "slept for 30"
6
1913
by: Roger Heathcote | last post by:
sjdevnull@yahoo.com wrote: <snip> Fair point, but for sub processes that need to be in close contact with the original app, or very small functions that you'd like 100s or 1000s of it seems like a kludge having to spawn whole new processes build in socket communications and kill via explicit OS calls. I can't see that approach scaling particularly well but I guess there's no choice. Does anyone think it likely that the threading...
0
9936
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9791
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10846
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10412
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7970
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5990
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4610
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4215
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3231
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.