Data purging and Archival from VLDB

How to delete Records from a Very Large Tables ?

If we have to delete a large records based on some conditions like year wise - month wise from very large table and our SLA is without degrading system performance then we will come into problem .We can't use TRUNCATE TABLE and we may run out of disk space and then DELETE query fails and nothing is deleted.

The Real-Problem (from SQL Bible)

A DELETE executes as a transaction where it will not commit until the last record is deleted. DELETE physically removes rows one at a time and records each deleted row in the transaction log. If the number of records in a table is small, a straight out DELETE is fine, however, with a large table the DELETE will cause the transaction log to grow. When this happens the system IO performance is degraded. Also, the table will be locked which will affect the application. The person executing the DELETE query will usually panic because what seems to be a simple task takes a long time and there is no visible progress from Query Analyzer. If the transaction log runs out of disk space then the DELETE fails and the transaction rolls back and nothing ends up deleted.

A Solution:

A solution is to use the combination of a WHILE loop and @@ROWCOUNT to delete the records in manageable blocks

Below is a script that you can use to do bulk deletions.

select count(*) from prps_delete where left(erdat,4)='2012'

SET ROWCOUNT 10000

WHILE 1 = 1

      BEGIN

      

      -------------------------------------------------

      DELETE prps_delete WHERE left(erdat,4)='2012'

     

      IF @@ROWCOUNT = 0

            BREAK

      END

SET ROWCOUNT 0

(10000 row(s) affected)

(10000 row(s) affected)

(10000 row(s) affected)

(10000 row(s) affected)

.

.

.

Cont………….

(526 row(s) affected)

select count(*) from prps_delete where left(erdat,4)='2012'


Thanks
SumanJha_Accidental_DBA