What is Data Purging ?
Data purging is just deleting the old data from the tables in the database.
Many OLTP databases are in need of a data purging solution. Deleting data that is no longer used by the application makes it possible to reduce the database size and, consequently, the application response time. Keeping the database small will increase its manageability, boost the performance, and reduce the hardware demand.
Data cutoff interval (two months, 45 days, etc.) varies from app to app; therefore, it should be determined by the application / business owners and communicated to the development team.
How we Purge the data in SQL Server ?
Data purging should be accomplished through the blocked deletes to prevent table locking (Script 1).
Script For SQL Server 2000 databases
-- For SQL Server 2000 databases IF @@TRANCOUNT > 0 ROLLBACK SET ROWCOUNT 10 WHILE (1=1) BEGIN DELETE FROM <table name> WHERE <filter column> < '2008-01-01' IF @@ROWCOUNT = 0 BREAK END SET ROWCOUNT 0
Note: in above Script 10, is an arbitrary number and can be adjusted.
Script for SQL Server 2005/2008 / 2008 R2 databases
WHILE (SELECT COUNT(*) FROM <table name> WHERE <filter column> < '2008-01-01') > 0 BEGIN DELETE TOP 10 FROM <table name> WHERE <filter column> < '2008-01-01' END
Developers should be cognizant of referential integrity aspects and sequence the delete process to avoid the foreign key constraint violations by purging data in the child table(s) first.
Best Practice for Data Purging
If data purging has to occur on a regular basis, the aforementioned code should be placed in a SQL Server job scheduled to run periodically, preferably during the off-peak hours.