Automatic deletion of data after 2 years

Other SQL Server Topics
1

Hi guys! I am new!
I have the following problem. I use a database that also logs database activity. Can someone help me how to create a T-SQL script to delete all log data older than 2 years.

Thanks and greetings in advance!

2

DELETE TOP (5000)
FROM YourTable
WHERE convert(varchar(10),Datecolumn,112)< convert(varchar(10),dateadd(year,-2,getdate()),112)
GO 10000

2 Likes
3

Hello ahmeds08. thank you very much for your help. I am going to try it by Tuesday next week. I will inform you about my experiences!

4

hi

please see link .. if it helps

6

The question is related to deleting the old data residing inside the data files.

7

Hi ahmeds08, your T-SQL was great. Thank you again!
I also noticed that there is a foreign key of another table in my table whose record I want to delete during the action. This is varchar with the following contents 20200928061623140MUST

Do you still have a good idea for this?

8

you need to delete the records first from the child table by joining them to the referenced key with the same where condition that you are currently using, and then perform delete on main table.

DELETE FROM child
FROM childtable AS child
INNER JOIN maintable AS parent ON child.ParentId = parent.ParentId
WHERE condition;

1 Like
9

Lordy... Be REALLY careful in making such a general recommendation when you know nothing about the target table.

First, 50 million rows may not be enough, especially for older log tables. Your code also uses a NON-SARGable predicate that may not allow the use of an index seek followed by a very effective range scan. If it solves the problem in the first 1000 iterations, it will still continue and if it's doing scans to try to find the data, it can take a whole lot of resources because it will scan the table an additional 9000 times to do nothing. You also have no clue how much data will be deleted vs how much will remain and you could avoid nor do you have any idea about the log file backup schedule. You could be causing an explosion in the log file which, ironically, such loops are supposed to help prevent. Depending on the ratio of deletes to the keeps, nearly all log file activity could be avoided as well as the unnecessary scan loops, etc, etc.

2 Likes
10

Thanks for your inputs.
Regarding log explosion, we normally change the log backup frequency to much smaller value during such activities, so that the log is reusable and I think most of us do it.
I should have asked this questions before replying.

11

Thanks for responding... you're a good man. Yes to your last, especially since the OP has stated they're a newbie. But, you've already made the realization. Again, thanks for your response and taking it the right way.