Killing Excessive Blocking Transactions in SQL

I recently made a small error in a SQL Server parsing function that caused an endless loop. Unfortunately SQL doesn't escalate an error and will allow these transactions to go on endlessly.

I quickly realized the error and attempted to correct the SQL function. However since there were users stuck in this endless loop, my ALTER FUNCTION transaction was being blocked.

KILLing individual SPIDs didn't work since I could not keep up with the number of backup of user requests.

To circumvent this, I ran a process to KILL all queries blocking my SPID:


declare @bCont bit, @i int, @s varchar(100)
set @bCont = 1

while @bCont = 1
Begin
 select @i = blocked
 from    master..sysprocesses
 where    spid = 113

 if isnull(@i , 0) <> 0
   BEGIN
     set @bCont = 1
print @s
     SET @s = 'kill ' + convert(varchar(12), @i)
     exec (@s)
waitfor delay '00:00:00.05'
   END
 else
   SET @bcont = 0
end

Comments

VB.Net code to control mouse movement and click

VB.Net code to perform mouse movements and clicks. Include references at the top of the class code file to Windows interface libraries: Public Declare Auto Function SetCursorPos Lib "User32.dll" (ByVal X As Integer, ByVal Y As Integer) As Integer Public Declare Auto Function GetCursorPos Lib "User32.dll" (ByRef lpPoint As Point) As Integer Public Declare Sub mouse_event Lib "user32" Alias "mouse_event" (ByVal dwFlags As Integer, ByVal dx As Integer, ByVal dy As Integer, ByVal cButtons As Integer, ByVal dwExtraInfo As Integer) Some fixed constant values will be needed, so include these as basic names: Public Const MOUSEEVENTF_LEFTDOWN = &H2 Public Const MOUSEEVENTF_LEFTUP = &H4 Public Const MOUSEEVENTF_MIDDLEDOWN = &H20 Public Const MOUSEEVENTF_MIDDLEUP = &H40 Public Const MOUSEEVENTF_RIGHTDOWN = &H8 Public Const MOUSEEVENTF_RIGHTUP = &H10 Public Const MOUSEEVENTF_MOVE = &H1 This ...

Windows Firewall can not run because another program or service is running that might use the Network Address Translation component (IPNat.sys)

Windows Networking Firewall failure Error Upon trying to open and configure the Windows built-in Firewall, you receive the error: "Windows Firewall can not run because another program or service is running that might use the Network Address Translation component (IPNat.sys)" Cause is due to settings left in by "Routing and Remote Access" service. Even if the service is stopped, Windows will still report this error because the network card bindings are still being held by RRAS. Disable RRAS by opening the MMC for it and "Disable Remote Access and Routing". This can also be found by Right -clicking "My Computer", opening the Service and Application node. By Disabling RRAS in this way, the network protocol interface bindings are removed allowing for the Windows Firewall and Connection Sharing service to take over.

Google's Automated Search Query Capture

It's known that Google takes preventative measures to reduce automated use of their search engine. In fact, Googles terms of service restrict the use of automated queries. Normally human users with real browsers will not be suspect of such use and thereby should not trigger firewall rules that detect queries that appear to be automated. However I found myself in just that position. After running several varied queries, I came back to running the repeating a past query (through the browser drop down query history) and received the following : HTML Source Interesting to note is that the page response header is a 503 error code . I suspect this was triggered by my complex query, retrieving multiple pages or results, and repeated usage in a short period. Google knowledge base on this topic suggests that users that have this problem may also have a virus or other spyware on their computer or another in the network.

Dave's Techytips

Search This Blog