title: SQL Server Log full due to active transaction
body: <p>I have been trying to update a column in a table and I am getting the below error:</p>
  <blockquote>
  <p>The transaction log for database 'STAGING' is full due to 'ACTIVE_TRANSACTION'.</p>
  </blockquote>
  <p>I am trying to run the below statement :</p>
  <pre><code>UPDATE [STAGING].[dbo].[Stg_Encounter_Alias]
      SET
          [valid_flag]            = 1
  
      FROM  [Stg_Encounter_Alias] Stg_ea
      where [ACTIVE_IND] = 1
          and [END_EFFECTIVE_DT_TM] &gt; convert(date,GETDATE())
  </code></pre>
  <p>My table has approx 18 million rows. And the above update will modify all the rows. The table size is 2.5 GB. Also the DB is in simple recovery mode</p>
  <p>This is something that I'll be doing very frequently on different tables. How can I manage this?</p>
  <p>My Database size is as per below</p>
  <p><a href="https://i.stack.imgur.com/OwE3z.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/OwE3z.png" alt="enter image description here" /></a></p>
  <p>Below are the database properties!!! I have tried changing the logsize to unlimited but it goes back to default.</p>
  <p><a href="https://i.stack.imgur.com/dD5qE.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/dD5qE.png" alt="enter image description here" /></a></p>
  <p>Can any one tell me an efficient way to handle this scenario?</p>
  <p>If I run in batches  :</p>
  <pre><code>begin
  DECLARE @COUNT INT
  SET @COUNT = 0
  
  SET NOCOUNT ON;      
  DECLARE @Rows INT,
      @BatchSize INT; -- keep below 5000 to be safe
  
  SET @BatchSize = 2000;
  
  SET @Rows = @BatchSize; -- initialize just to enter the loop
  
  
  WHILE (@Rows = @BatchSize)
  BEGIN
    UPDATE TOP (@BatchSize) [STAGING].[dbo].[Stg_Encounter_Alias]
      SET
          [valid_flag]            = 1
  
      FROM  [Stg_Encounter_Alias] Stg_ea
      where [ACTIVE_IND] = 1
          and [END_EFFECTIVE_DT_TM] &gt; convert(date,GETDATE())
    SET @Rows = @@ROWCOUNT;
  END;
  end
  </code></pre>