Database Time Out Issue

[rcombs4]

Hey guys, i know this issue is not VB.net related but I figured I'd post here before joining some random forum.


Our DB Set Up:
We have 2 DBs using SQL Server 2005. Both these DBs are set up with mirroring to our backup server. Our set up uses a third server as a witness server. The witness server is what pushes the DB to the back up server if it cannot connect to the primary server.

The Issue:
From time to time on one of our DBs we will get time outs. The time outs seem to only occur on two tables. These tables have the standard FK association, so when we insert into one we always insert into the other right after. But when these time outs happen we cannot insert into the first table, and doing basic queries on it is extremely slow, if we get any results back at all. We can solve the issue manually by forcing the DB to the back up server and then forcing the DB back to the primary server.

These two tables are indexed and we have ran a script to re-index them occasionally. I'm at quite a loss, everything I can think of that would cause this issue would not explain why rolling the DB to the back up would solve the issue.

Thanks for any help you can give me.

 

[cjard]

How many uncommitted transactions are active on the tables in question? I also recall that older versions of SQLS locked rows at the page level rather than the row level so a user having an open edit in a row in the same page (e.g. editing in a query tool or performing some update that locks the page) can cause other rows to become locked. Queries often sit and wait in these cases, unless a flag has been set (in oracle we do this by way of specially formatted comments in the sql) that the DB shouldnt wait..

I'd first research a concurrency issue here..