April 11, 2012 at 8:48 am
Good Morning,
Since last week , we have been trying to hunt down an issue.
Here is an abstract.
One SQL Server 2005 server has about 5 different databases.
A .Net based web service interacts with one of the DB on this server using ODBC connection.
Intermittently at least once in a day, we see that the web service starts to timeout and the trace log indicates the SQL timeout exception.
When we execute sp_who2 we see that the queries on this DB are being blocked by queries running on the other DB on the same server.
When we kill the blocking queries, everything gets back to normal for a day or two and this issue crops back again.
There is not fixed time, it is so random that it is throwing me out of my understanding to determine at least the pattern or the queries that are timing out.
When i try to execute some select on the locked DB using Management studio, i get back results normally.
I am a .Net developer having some decent knowledge on SQL side for development efforts, but this issue i have never seen before or unable to understand what could be causing it.
I would really appreciate any help or advice in this regards
Thanks
April 11, 2012 at 8:55 am
You need to investigate the queries being run in the other database. If they are blocking queries in the database being accessed by the web app, it sounds like there are cros database queries being run.
April 11, 2012 at 9:04 am
You should also look at sys.dm_db_tran_locks to see what the blocking session is locking
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 11, 2012 at 9:20 am
Thanks for the replies, i will dig deeper into it next time when it happens and will post my observations.
Thanks once again.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply