title: SQL Azure - One session locking entire DB for Update and Insert
body: <p>SQL Azure issue.</p>
  <p>I've got an issue that manifests as the following exception on our (asp.net) site:</p>
  <blockquote>
  <p>Timeout expired.  The timeout period elapsed prior to completion of
  the operation or the server is not responding. The statement has been
  terminated.</p>
  </blockquote>
  <p>It also results in update and insert statements never completing in SMSS. There aren't any X or IX locks present when querying: <code>sys.dm_tran_locks</code> and there are no transactions when querying <code>sys.dm_tran_active_transactions</code> or <code>sys.dm_tran_database_transactions</code>.</p>
  <p>The problem is present for every table in the database but other databases on the same instance don't cause the problem. The duration of the issue can be anywhere from 2 minutes to 2 hours and doesn't happen at any specific times of day.</p>
  <p>The database is not full.</p>
  <p>At one point this issue didn't resolve itself but I was able to resolve the issue by querying <code>sys.dm_exec_connections</code> finding the longest running session, and then killing it. The odd thing is, that the connection was 15 minutes old, but the lock issue had been present for over 3 hours.</p>
  <p>Is there anything else I can check?</p>
  <p><strong>EDIT</strong></p>
  <p>As per Paul's answer below. I'd actually tracked down the problem before he answered. I will post the steps I used to figure this out below, in case they help anyone else.</p>
  <p>The following queries were run when a &quot;timeout period&quot; was present.</p>
  <pre><code>select *  from sys.dm_exec_requests
  </code></pre>
  <p><img src="https://i.stack.imgur.com/wzWlM.png" alt="Request Stats" /></p>
  <p>As we can see, all the WAIT requests are waiting on session 1021 which is the replication request! The <code>TM Request</code> indicates a DTC transaction and we don't use distributed transactions. You can also see the wait_type of <code>SE_REPL_COMMIT_ACK</code> which again implicates replication.</p>
  <pre><code>select * from  sys.dm_tran_locks
  </code></pre>
  <p><img src="https://i.stack.imgur.com/AyBo2.png" alt="enter image description here" /></p>
  <p>Again waiting on session 1021</p>
  <pre><code>SELECT * FROM sys.dm_db_wait_stats ORDER BY wait_time_ms desc
  </code></pre>
  <p><img src="https://i.stack.imgur.com/QZnKl.png" alt="enter image description here" /></p>
  <p>And yes, <code>SE_REPL_CATCHUP_THROTTLE</code> has a total wait time of 8094034
  ms, that is 134.9minutes!!!</p>
  <p>Also see the following forum for details on this issue.
  <a href="http://social.technet.microsoft.com/Forums/en-US/ssdsgetstarted/thread/c3003a28-8beb-4860-85b2-03cf6d0312a8" rel="noreferrer">http://social.technet.microsoft.com/Forums/en-US/ssdsgetstarted/thread/c3003a28-8beb-4860-85b2-03cf6d0312a8</a></p>
  <blockquote>
  <p>I've been given the following answer in my communication with
  Microsoft (we've seen this issue with 4 of our 15 databases in the EU
  data center):</p>
  <p>Question: Have there been changes to these soft
  throttling limits in the last three weeks ie since my problems
  started?</p>
  <p>Answer: No, there has not.</p>
  <p>Question: Are there ways we can
  prevent or be warned we are approaching a limit?</p>
  <p>Answer: No. The issue
  may not be caused by your application but can be caused by other
  tenants relying on the same physical hardware. In other words, your
  application can have very little load and still run into the problem.
  In other words, your own traffic may be a cause of this problem, but
  it can just as well be caused by other tenants relying on the same
  physical hardware. There's no way to know beforehand that the issue
  will soon occur - it can occur at any time without warning. The SQL
  Azure operations team does not monitor this type of error, so they
  won't automatically try to solve the problem for you. So if you run
  into it you have two opitions:</p>
  <ol>
  <li><p>Create a copy of your db and use that and hope the db is placed on another server with less load.</p>
  </li>
  <li><p>Contact Windows Azure Support and inform the about the problem and let them do Option 1 for you</p>
  </li>
  </ol>
  </blockquote>