08-29-08, 13:19 #1Registered User
- Join Date
- Aug 2008
Unanswered: help debugging an application problem
I’m interested in some advice on isolating an application problem
My company is running an ERP system using SQL2000 sp3 as the backend DB.
It uses a combination of C++ and Powerbuilder as the front end.
I’m running WINDOWSserver2003 (no service pak) on an HP DL530 server with 4GB ram. Mirrored command disk.
We have several separate company databases.
The main DB is 7gb, the 2nd biggest is 3GB – and several other much smaller ones.
There are maybe 100+ users on the system at any time.
Maybe 250+ sql processes running.
We’ve been experiencing a problem – where users in the main DB get ‘locked out’ of processing. Basically their erp transaction session will hang for up to 5+ mins.
I am assuming this is response to some DB issue – but it may not so... i can't tell- but i would like to confirm/deny.
Also just recently - we determined that users in the other DBs are NOT experiencing any 'lock out'. So it's got to be the DB, no?
anyway, the database server main disk (RAID-5 - 72GB) had just been ‘rebuilt’. Ie - we restored all of the databases from backup. So fragmentation is minimal.
HP and hardware vendor have yet to uncover any possible hardware fault. Which my gut feel, is true.
So my question is … can you provide any hint of actions that I can take before – or during – one of these episodes that would help me isolate or eliminate the source of the problem. Be that the SQL server or data base?
I've used some sql activity scans and perfmon on the server - but i think maybe there is something easier that i can do - to confirm or deny that the problem in with the database itself. or at least to isolate where real the “problem” lies.
08-29-08, 13:36 #2Annie's Dog Walker
Provided Answers: 6
- Join Date
- Nov 2004
- on the wrong server
during these episodes, open the query analyzer and run sp_who2. look for SPIDS in the BLKBY column. Run that SPID through DBCC INPUTBUFFER. If the problem is blocking and it sounds like it might be, This will be your problematic SQL.“If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.
09-02-08, 09:06 #3Registered User
- Join Date
- Mar 2003
- Reading, UK
Also check if there are any open transactions at the same time.