title: Deleting rows from SQL Server increased the database size
body: <p>I have written some code in C# that deletes records from a database. It's your straightforward SQL delete code enclosed in a transaction.</p>
  
  <pre><code>using (SqlTransaction trans = conn.BeginTransaction()) {
      //some delete code
      trans.Commit();
  }
  </code></pre>
  
  <p>I have deleted about 4 million rows across 4 tables in my database. When I checked the database backup size of both DBs via Windows Explorer, the size has dramatically increased about thrice</p>
  
  <ul>
  <li>Before deletion: <strong>7GB</strong></li>
  <li>After deletion: <strong>28GB</strong></li>
  </ul>
  
  <p>I confirmed that both the <strong>MDF</strong> and the <strong>LDF</strong> files increased in size. I also noticed that some tables that are not affected by the delete have their index size increased. </p>
  
  <p><strong>After deletion:</strong></p>
  
  <p><a href="https://i.stack.imgur.com/PX8S7.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/PX8S7.png" alt="enter image description here"></a></p>
  
  <p><strong>Before deletion:</strong></p>
  
  <p><a href="https://i.stack.imgur.com/gjAST.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/gjAST.png" alt="enter image description here"></a></p>
  
  <p>My goal is to delete records to reduce the database size but it increased instead. Can you tell me why the size is increasing?</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p><strong>After deletion:</strong></p>
  
  <ul>
  <li><strong>database_size:</strong> 61050.69 MB</li>
  <li><strong>unallocated space:</strong> 9592.60 MB</li>
  <li><strong>reserved:</strong> 303512 KB</li>
  <li><strong>data:</strong> 207040 KB</li>
  <li><strong>index_size:</strong> 53024 KB</li>
  <li><strong>unused:</strong> 43448 KB</li>
  </ul>
  
  <p><strong>Before deletion:</strong></p>
  
  <ul>
  <li><strong>database_size:</strong> 10067.88 MB</li>
  <li><strong>unallocated space:</strong> 16.69 MB</li>
  <li><strong>reserved:</strong> 7290176 KB</li>
  <li><strong>data:</strong> 2937656 KB</li>
  <li><strong>index_size:</strong> 4324848 KB</li>
  <li><strong>unused:</strong> 27672 KB</li>
  </ul>