title: SQL Server Large Transaction locking all tables
body: <p>Thanks for taking the time to read my question. We have built a system using T-SQL that processes (large) quantities of data. When we start processing this data, this operation usually takes around 15 minutes to complete. During this time, for some reason, it seems that there is a lock on all objects in the database. To some extend I understand that I cannot query tables that are being written to. But also SQL Server gives me a time-out lock message when I refresh the list of tables for example (using F5 on the Tables node in the object explorer).</p>
  
  <p>My code works based on the following template:</p>
  
  <pre><code>CREATE PROCEDURE Sample
  
  as
  
  SET NOCOUNT, XACT_ABORT ON
  
  BEGIN TRY
  
  BEGIN TRANSACTION
  
  ...
  
  COMMIT TRANSACTION
  
  END TRY
  
  BEGIN CATCH
      BEGIN
          PRINT 'STORED PROCEDURE - Returned errors while processing'
          ROLLBACK TRANSACTION
      END
  END CATCH
  </code></pre>
  
  <p>Now, when I start this I can perform SELECT WITH (NOLOCK) queries on any table. But I cannot view definitions on views, stored procedures or basically do any other kind of operation in the database.</p>
  
  <p>I have already set the READ UNCOMMITTED isolation level setting, but that doesn't seems to make a difference.</p>
  
  <p>Appreciate any help on this matter.</p>
  
  <p>Kind regards,</p>