Saturday, February 25, 2012

Need urgent help

Hi

I have a strange situation. We have a middle teir application (j2ee appserver) that uses connection pooling.

The client was working fine when all of a sudden it hangs.

I have checked the sp_lock and sp_who2 and there has been no locking/blocking whatsover. This problem stays until we restart the app server. Is there any way that I can further dig into this problem and find it out if it is the db that is causing the issue.

I used sql profiler and I could see some log entries made into the database while client is hanging after that. Also sp_who2 lists the spid for the middle tier connection. It does not however show any locks associated with it. The last batch date however is updated every time the client is opened (when it hangs).

Any pointers as to why this is happening,how to diagnose and prevent this is extermely helpful

From what you're saying, it looks like the problem does not reside in the database. If sp_who2 does not return any values in the BlkBy column then probably it is not a db problem.

Besides locking, another thing that can happen is to have an infinite loop in your T-SQL code. You can verify this by running SQL Profiler.

Just to make sure that the problem does not reside in your database you can kill your db connection (by using Kill SPID in Query Analyzer) and see if the client app still does not respond. If it still doesn't then the problem is in your client app and not in the database.

Hope it helps,

Doru

|||

You might be right......I had also killed the spid on the backend and the client still hangs.....Probably it has got to to do with the txactional setting placed on the middle tier connection,,,,Besides there are no infinite loops on the back end,,,,

Thanks for the post....

No comments:

Post a Comment