How to Purge data in SQL Server ?

Leave a comment (0) Go to comments

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.

EOF - How to Purge data in SQL Server ?, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.