Purge by deleting all records older than 180 days from the current date in SQL Server

Question

In SQL Server 2008 R2 I have a table (database size is 450 GB) with over a billion rows, which I'd like to purge by deleting all records older than 180 days counting from the current date. Any help here would be appreciated!

I'm using the following query :

DELETE FROM table name 
WHERE column name < '2015-01-01'

But it is taking too much time. Is there any maintenance plan or any query so that I can delete the data fast?

How big percentage of the rows is older than 180 days? Maybe it's easier to move them to a different table, truncate the original and rename the table / copy the rows back. Otherwise you might want to delete smaller number of rows in a batch — James Z, Feb 05 '16 at 16:53
whatever you end up doing would have to use SOME kind of date filtering anyways, and end up taking the same amount of time. though maybe it's quicker to go the other direction. copy any records `>= 2015-01-01` to another table, then drop the original one - especially if the newer records are a tiny subset of the entire billion. — Marc B, Feb 05 '16 at 16:55
dear friends....added the image row size and data size in my database. — Shivprasad Waychal, Feb 05 '16 at 17:01

score 1 · Answer 1 · answered Feb 05 '16 at 16:56

Yes. When you want to delete records, regularly, that are old, then the right approach is to use partitioning.

This is a large topic. You can start to learn about it through the documentation.

The key idea is that each partition is a separate store of the data. An entire partition can be dropped without logging, incurring very little overhead.

In your case, I would probably suggest a separate partition for each month.

score 1 · Answer 2 · edited May 23 '17 at 12:13

It's taking a long time because (in part) all of those deletes are going into one gargantuan transaction. You need to break it down into smaller chunks (transactions) and periodically commit. It'll still take a long time but the impact on your server will be lessened. See https://stackoverflow.com/a/28324562/1324345 and the blog post it references, http://sqlperformance.com/2013/03/io-subsystem/chunk-deletes

SET NOCOUNT ON;

DECLARE @r INT;

SET @r = 1;

WHILE @r > 0
BEGIN
  BEGIN TRANSACTION;

  DELETE TOP (100000) -- this will change
    table
    WHERE column name < '2015-01-01' ;

  SET @r = @@ROWCOUNT;

  COMMIT TRANSACTION;

  -- CHECKPOINT;    -- if simple
  -- BACKUP LOG ... -- if full
END

If your table is partitioned, it can be much easier and faster.

TT. · Accepted Answer · 2016-02-05T19:21:51.757

1

One approach that might save you some time:

Start off with taking a backup (you never know)
Insert the rows you want to keep into a temporary table (make sure you have enough room on disk for tempdb)
TRUNCATE the table to remove all rows quickly (this statement will execute instantly)
Insert the rows from the temporary table back into your source table

INSERT INTO #keep SELECT * FROM table_name WHERE column_name>='2015-01-01';
TRUNCATE TABLE table_name;
INSERT INTO table_name SELECT * FROM #keep;
DROP TABLE #keep;

edited Feb 05 '16 at 19:21

answered Feb 05 '16 at 19:16

TT.

15,774
6
47
88

thanks for your help.....but this sys log,security data is coming daily in that database. so we need to delete the data daily basis. – Shivprasad Waychal Feb 06 '16 at 03:47
@ShivprasadWaychal You could schedule this daily at a time where the data does not have to be accessible. I answered to this part of your question: *"Is there any [...] any query so that I can delete the data fast?"*. But I agree with Gordon's answer, perhaps that would be a better long term solution. – TT. Feb 06 '16 at 05:37
@ShivprasadWaychal But back to my answer. Once you have deleted the whole history the "quick way" as in my answer, daily deletes won't take as much time any more. You would schedule a daily delete query - the normal way, a simple `DELETE FROM your_table WHERE date_col – TT. Feb 06 '16 at 06:15

Purge by deleting all records older than 180 days from the current date in SQL Server

3 Answers3