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 - 2006-04-11 : 09:31:17
|
| Trip writes "I am the IT director for a collection agency in Jacksonville, FL. and I also own a successful IT consulting company that services over 90 clients in the North East Florida region. One particular Skill I do not have experience in is SQL. I know how to set one up, but that is about it. Recently the collection company I work for started to have employees complaining about time out issues and there collection software locking up. When they would complain, I would look in the Enterprise Manager and find that certain SPIDs were being blocked by another and one or two SPIDs were blocking. A simple fix was to kill the blocking process and have everyone get out and then back in the software and everything seemed fine. That was until this started happening everyday and numerous times throughout the day. The problem I am having now is going back and forth with the software company that developed the collection software and having to argue with them on a solution. The SQL server is a Quad Zeon processor, 8GB memory Dell server with plenty of SCSI space. It runs Windows 2003 Enterprise server with SQL 2000 Enterprise server w/ SP3. I have enabled the server with the /PAE switch for full memory availability and the server just rocks. The collection software company is consistently trying to find out what is wrong with the server instead of them looking at the problem being there own software and I am taking the blame. What do I do next to find out where the problem is and how do I go about fixing it. PLEASE HELP!Thanks, " |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2006-04-11 : 09:56:05
|
| Set up a profile trace for all activity on that database and when you get a lock, you can see what that SPID was doing (or trying to do) at the time.. This should tell the software company where the error is happening in their code.. |
 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-04-11 : 21:21:02
|
| apply the latest patch for the sql server :sp4this might not help but always keep your patches up to date--------------------keeping it simple... |
|
|
|
Jhalmans
Microsoft SQL Server Product Team
12 Posts |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-04-15 : 09:44:24
|
| Blocking is not a hardware or setup issue - although it might be helped by the server running faster the underlying problem will still be there - and you can waste a lot of money and time improving the hardware when a simple design change would have a lot more effect.Put this on the server on the master databasehttp://www.nigelrivett.net/SQLAdmin/sp_nrInfo.htmlWhen you run sp_nrinfo it should show all the commands being executed on the server and the blocking spid and it's command. This will show you what is doing the blocking and what to look at.If nothing else it will show you what you need to kill.==========================================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. |
|
|
|
|
|
|
SPID Blocking