delete records in batches‌‌ and recursive for tables

• s.ravisankar

  SSChasing Mays

  Points: 647

  More actions

  December 6, 2018 at 8:12 am

  #382423

  I have number of tables , I want to purge record based 3 month timeline.

  eg:

  select * from sys.tables where name like 'abc%'

  I have 10 tables starting with abc

  each table have millions of record

  I want to delete records in batches and recursive for those 10 tables

  Thanks in Advance 🙂

• Phil Parkin

  SSC Guru

  Points: 247037

  More actions

  December 6, 2018 at 8:29 am

  #2015668

  s.ravisankar - Thursday, December 6, 2018 8:12 AM

  I have number of tables , I want to purge record based 3 month timeline.

  eg:

  select * from sys.tables where name like 'abc%'

  I have 10 tables starting with abc

  each table have millions of record

  I want to delete records in batches and recursive for those 10 tables

  Thanks in Advance 🙂

  This is a requirement (of sorts). What is your question?

  ---

• Jonathan AC Roberts

  SSCoach

  Points: 18387

  More actions

  December 6, 2018 at 8:34 am

  #2015672

  Just write a small block of code for each of your 10 tables:
  DECLARE @BatchSize int = 100000
DECLARE @RowsDeleted int = @BatchSize 
WHILE @RowsDeleted <> @BatchSize BEGIN
  DELETE TOP(@BatchSize)
  FROM abcTable1
 WHERE abcTable1.DateStamp < DATEADD(month,-3,GETDATE())
 SET @RowsDeleted = @@ROWCOUNT
END
GO
  You might find it helpful to have an index on the Date column you are checking against if you are doing lots of small batches to do the delete.

• Jeff Moden

  SSC Guru

  Points: 1004504

  More actions

  December 6, 2018 at 8:38 am

  #2015675

  s.ravisankar - Thursday, December 6, 2018 8:12 AM

  I have number of tables , I want to purge record based 3 month timeline.

  eg:

  select * from sys.tables where name like 'abc%'

  I have 10 tables starting with abc

  each table have millions of record

  I want to delete records in batches and recursive for those 10 tables

  Thanks in Advance 🙂

  So check the FKs to find out what order of deletes you have and either write the deletes or write some code to make the dynamic SQL to do the deletes.  If you do it in the correct order, then you can have a nested loop to either delete from all the tables on an increasing date pattern or work on one table until it's complete.

  Also, how much of the biggest tables will actually be deleted?  There may be a better way to do the initial large delete but it will require an outage and some temporary disk space.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• s.ravisankar

  SSChasing Mays

  Points: 647

  More actions

  December 6, 2018 at 7:52 pm

  #2015717

  Jonathan AC Roberts - Thursday, December 6, 2018 8:34 AM

  Just write a small block of code for each of your 10 tables:
  DECLARE @BatchSize int = 100000
DECLARE @RowsDeleted int = @BatchSize 
WHILE @RowsDeleted <> @BatchSize BEGIN
  DELETE TOP(@BatchSize)
  FROM abcTable1
 WHERE abcTable1.DateStamp < DATEADD(month,-3,GETDATE())
 SET @RowsDeleted = @@ROWCOUNT
END
GO
  You might find it helpful to have an index on the Date column you are checking against if you are doing lots of small batches to do the delete.

  Thanks
  Here it's for 1 table but if I found 10 table from query then same should happen for 10 tables

• s.ravisankar

  SSChasing Mays

  Points: 647

  More actions

  December 6, 2018 at 7:54 pm

  #2015718

  s.ravisankar - Thursday, December 6, 2018 7:52 PM

  Jonathan AC Roberts - Thursday, December 6, 2018 8:34 AM

  Just write a small block of code for each of your 10 tables:
  DECLARE @BatchSize int = 100000
DECLARE @RowsDeleted int = @BatchSize 
WHILE @RowsDeleted <> @BatchSize BEGIN
  DELETE TOP(@BatchSize)
  FROM abcTable1
 WHERE abcTable1.DateStamp < DATEADD(month,-3,GETDATE())
 SET @RowsDeleted = @@ROWCOUNT
END
GO
  You might find it helpful to have an index on the Date column you are checking against if you are doing lots of small batches to do the delete.

  Thanks
  Here it's for 1 table but if I found 10 table from query then same should happen for 10 tables

  I want with nested loop recursive for all tables

• s.ravisankar

  SSChasing Mays

  Points: 647

  More actions

  December 6, 2018 at 7:58 pm

  #2015719

  Jeff Moden - Thursday, December 6, 2018 8:38 AM

  s.ravisankar - Thursday, December 6, 2018 8:12 AM

  I have number of tables , I want to purge record based 3 month timeline.

  eg:

  select * from sys.tables where name like 'abc%'

  I have 10 tables starting with abc

  each table have millions of record

  I want to delete records in batches and recursive for those 10 tables

  Thanks in Advance 🙂

  So check the FKs to find out what order of deletes you have and either write the deletes or write some code to make the dynamic SQL to do the deletes.  If you do it in the correct order, then you can have a nested loop to either delete from all the tables on an increasing date pattern or work on one table until it's complete.

  Also, how much of the biggest tables will actually be deleted?  There may be a better way to do the initial large delete but it will require an outage and some temporary disk space.

  Thanks
  Yes we need to do based on FK,  large table has 50millions minimum,  I tested with batch deleting it's not increasing logs 
  If any other approach let me know

• pietlinden

  SSC Guru

  Points: 63535

  More actions

  December 6, 2018 at 9:19 pm

  #2015721

  The whole point of doing the delete in batches is that it doesn't increase the size of the log.

  or am I missing something?

  As for processing a bunch of tables, you could use a cursor to loop through them, pass the table name to the procedure that does the delete and uses dynamic SQL to do the job. Something like this maybe?

  DECLARE @TableName VARCHAR(100);
  DECLARE TableList_Cursor CURSOR FORWARD_ONLY
  FOR
  SELECT [name]
  FROM MyDatabase.sys.tables st
  --WHERE st.[name] LIKE 'abc%';

  OPEN TableList_Cursor

  FETCH NEXT FROM TableList_Cursor INTO @TableName;

  WHILE @@FETCH_STATUS = 0
  BEGIN
          EXEC usp_TruncateMyTable @TableName;   -- usp_TruncateMyTable is a stub for the code Jonathan provided (just with @TableName as an input parameter.
          PRINT @TableName + ' has been processed.';
          FETCH NEXT FROM TableList_Cursor INTO @TableName;
  END

  CLOSE TableList_Cursor
  DEALLOCATE TableList_Cursor

• Jeff Moden

  SSC Guru

  Points: 1004504

  More actions

  December 7, 2018 at 7:02 am

  #2015749

  pietlinden - Thursday, December 6, 2018 9:19 PM

  The whole point of doing the delete in batches is that it doesn't increase the size of the log.

  or am I missing something?

  I believe that he was just confirming that the log size wasn't increasing, which is a good thing.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• s.ravisankar

  SSChasing Mays

  Points: 647

  More actions

  December 17, 2018 at 5:06 am

  #2016475

  Hi All
  Thanks for your reply

  I have checked with direct query then its not increasing log

  If I am having dynamic query then its increasing log

  Please give some suggestion

• Jeff Moden

  SSC Guru

  Points: 1004504

  More actions

  December 17, 2018 at 8:13 am

  #2016515

  s.ravisankar - Monday, December 17, 2018 5:06 AM

  Hi All
  Thanks for your reply

  I have checked with direct query then its not increasing log

  If I am having dynamic query then its increasing log

  Please give some suggestion

  My suspicion is that you've done something different in the dynamic query.  I suppose it's possible but I've never seen such a thing before. Log file usage shouldn't increase just because the deletes are dynamic.  Either that or you're measuring the impact on the log file the wrong way when using the "direct query".  Not sure, though.  There's just not enough information to go on.

  Shifting gears a bit, do the tables you're deleting from have IDENTITY columns?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)