CODEWITHSUNDEEP
sql-server-2008sql-delete
Dnyan Waychal
Dnyan Waychal

Reputation: 1418

Deleting large amount of data from SQL Server database

I am trying to purge large amounts of historical data from our database for any data older than 13 months.

I have written a stored procedure for truncation, however when I try to run them I have two problems.

Problem 1 - the deletion fills up the database transaction logs and crashes the database when the disk drives run out of space.

Problem 2 - The script itself takes a long time to execute.

Below are the statements in the SP:

DELETE FROM Header_Table WHERE Date_DT < @date

The Header_Table table has a ON DELETE CASCADE relationship with 10 other tables. That means when I fire the above query it deletes the data from the Header_Table table as well as from the 10 tables reference by Header_Table

  1. And in the second statement, I am inserting the data from the Information_Table table to temp table.

    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

  2. In the below statement, I am actually deleting the data from the Information_Table

     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

Upvotes: 0

Views: 1119

Answers (2)

HLGEM
HLGEM

Reputation: 96600

How often are the transaction logs being backed up? If you delete in batches with each batch in it's own transaction, frequent transaction log backups should prevent the transaction log from filling up unless your system is not set up to back them up (or only does so on an inappropraitely long schedule). Note transaction log backups are entirely different from database backups and should happen frquently in a transactional system - ours are backed up every 15 minutes. Also because you have cascading delets (which I personally would never have allowed), you will probaly need a fairly small batch.

Upvotes: 0

AnandPhadke
AnandPhadke

Reputation: 13506

In Such cases you should always delete data in chunks.What I suggest is,delete data in batches.

Upvotes: 3

Related Questions