blocking

Question

blocking

bluepaperbag

SSCrazy

Points: 2055
More actions
August 14, 2007 at 8:53 am

#118164

If there is a blocking. Will the Stored Procedure ever finish?
http://farm2.static.flickr.com/1124/1116393766_c37383a537_o.jpg

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Anders Pedersen SSChampion Points: 11410 More actions · Answer 1

Depends no the block. Most blocks in SQL are transient in nature, so as long as they go away before the timeout the procedure should finish

bluepaperbag SSCrazy Points: 2055 More actions · Answer 2

killed the blocking.

Server is still very slow after 6 hours. Even rebooted it. Any other ideas please?

steve smith-401573 SSChampion Points: 10889 More actions · Answer 3

Server is slow? or query is slow? Provide examples of code, information on keys, etc. Probably either humongous table size or improperly formed query. outer cross joins will require tons of resources.

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 4

Please note that I say this without any information on your code, keys, server etc but when you killed the blocking process if it was a long running piece of code .. say an insert or an update .. then it will also take a long time rolling it back. Your server (and the process) will continue to have problems until the rollback is complete.

Again .. I have no real information .. its just a guess.

Ken

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

bluepaperbag SSCrazy Points: 2055 More actions · Answer 5

Developers are executing queries which never finish. Thus causing blocking. They have told me, they executed the code before without problems.

The server is fine now. This started to happened before yesterday at 6PM, one of the developer told me his statement never finished executing. It was taking 7 hours, normally takes an hour. Then at 3AM a sql job blocking, then at 4AM another sql job blocking..

I cancelled the jobs, rebooted the box. Developers back to work, after 10 minutes started to slow down again. Then today, the slow down diminished completely.

Now, I wonder how could i find the cause of this problem. I'm not sure if it was the query executed by the developer as he told me he executed it before without problems.

steve smith-401573 SSChampion Points: 10889 More actions · Answer 6

Well, did you happen to ask the developer what he changed between the last time and this time he ran the query? Capturing the history after the fact - you can search for that phrase and find other threads on this forum that have suggested strategies and attempts.

Good hunting!

bluepaperbag SSCrazy Points: 2055 More actions · Answer 7

steve,

the result shows none for the phrase "Capturing the history after the fact"