Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-10-25 : 08:55:51
|
| Tom writes "We have a software application that uses SQL as the database. On busy days at the End User sites, SQL locks up. See report from System Admin below:>Next with her SQL lock up issues this is what she says about that:The only thing I could see was the spids. I don’t know if this will help your programmer but here it is: Spid 52 (blocked by 58)Spid 54 (blocking)Spid 55 (blocked by 58)Spid 58 (blocked by 54)This created a deadlock and therefore, required a reboot of the server.<Can you possibly give me some reasons that the spids are locking up? Any help sending us in the right direction will help!Thank you!!" |
|
|
JohnDeere
Posting Yak Master
191 Posts |
Posted - 2004-10-25 : 09:06:13
|
| SQL Server will generally resolve a deadlock without a reboot. If you blocking issues SQL Server will wait until one of the connections times out.Deadlocks and Blocking are generally caused by poor handling of SQL transctions in the application code. Uuse SQL profiler to trace the deadlocks. This should point you to the area of code that needs to be modified.Lance Harra |
 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-25 : 23:08:01
|
quote:
Originally posted by JohnDeere
SQL Server will generally resolve a deadlock without a reboot. If you blocking issues SQL Server will wait until one of the connections times out.Deadlocks and Blocking are generally caused by poor handling of SQL transctions in the application code. Uuse SQL profiler to trace the deadlocks. This should point you to the area of code that needs to be modified.Lance Harra
no need to reboot the server, restarting the sql service will have the same effect, but before doing that you may want to issue a kill command, example: kill 54which is causing the block. but before killing, double click the spid to check what it's running and determine source. most often, you need to tune/optimize the query--------------------keeping it simple... |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-10-25 : 23:32:33
|
| This isn't a deadlock - you just have a blocking spid.Put this in masterhttp://www.nigelrivett.net/sp_nrinfo.htmlIt will show you the blocking spid and the command it is executing and who is executing it (maybe).Look at the last batch time as you may want to leave it blocking to completion if it doesn't take long - maybe be something that a user needs to run an will just run it again and block if you kill it.It's importatnt to see what is blocking and try to do something about it (like fixing some code or telling a user not to do it again) - although just killing it when it happens may be acceptable.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
SQL locking up