title: Deleting large amount of data from SQL Server database
body: <p>I am trying to purge large amounts of historical data from our database for any data older than 13 months. </p>
  
  <p>I have written a stored procedure for truncation, however when I try to run them I have two problems.</p>
  
  <p>Problem 1 - the deletion fills up the database transaction logs and crashes the database when the disk drives run out of space.</p>
  
  <p>Problem 2 - The script itself takes a long time to execute. </p>
  
  <p>Below are the statements in the SP:</p>
  
  <pre><code>DELETE FROM Header_Table WHERE Date_DT &lt; @date
  </code></pre>
  
  <p>The <code>Header_Table</code> table has a <code>ON DELETE CASCADE</code> relationship with 10 other tables. That means when I fire the above query it deletes the data from the <code>Header_Table</code> table as well as from the 10 tables reference by <code>Header_Table</code></p>
  
  <ol>
  <li><p>And in the second statement, I am inserting the data from the <code>Information_Table</code> table to temp table.</p>
  
  <pre><code>INSERT INTO Temp_Table (Key, AccNum, Exp, Name_VC) 
     SELECT  
        in.Key AS CRD_NFO_CIK, 
        in.Acct_Num AS CRD_NFO_ACC, 
        in.Exp AS CRD_NFO_EXP,
        in.Name_on_Card_VC AS CRD_NFO_NAME    
     FROM 
        Information_Table in
     LEFT OUTER JOIN 
        Card_T crd ON in.Key = crd.Card_Info_Key
     LEFT OUTER JOIN 
        Business_T business ON in.Key = business.CC_Info_Key 
     LEFT OUTER JOIN 
        Con_T contr ON in.Key = contr.Card_Info_Key 
     LEFT OUTER JOIN 
        Customer_Payment_T customer ON in.Key = customer.Card_Info_Key 
     LEFT OUTER JOIN 
        Temp_Table Temp ON in.Key = Temp.Key
     WHERE 
        Temp.Key IS NULL AND
        crd.Card_Info_Key IS NULL AND 
        business.CC_Info_Key IS NULL AND 
        contr.Card_Info_Key IS NULL AND
        customer.Card_Info_Key IS NULL
  </code></pre></li>
  <li><p>In the below statement, I am actually deleting the data from the Information_Table</p>
  
  <pre><code> DELETE info  
   FROM Information_Table in    
   LEFT OUTER JOIN Card_T crd ON in.Key = crd.Card_Info_Key    
   LEFT OUTER JOIN Business_T business ON in.Key = business.CC_Info_Key     
   LEFT OUTER JOIN Con_T contr ON in.Key = contr.Card_Info_Key     
   LEFT OUTER JOIN Customer_Payment_T customer ON in.Key = customer.Card_Info_Key  
   WHERE 
      crd.Card_Info_Key IS NULL AND 
      business.CC_Info_Key IS NULL AND 
      contr.Card_Info_Key IS NULL AND
      customer.Card_Info_Key IS NULL
  </code></pre></li>
  </ol>