I'm making a web application in aspnet, and I created a table in the database to insert notifications. I would like to know if there is any way to automatically delete data from this table that is older than 30 days. Is there any way this can be done?

CodePudding user response：

I would like to know if there is any way to automatically

SQL Server build-in tool for schedule tasks (also called jobs) is the SQL Server Agent.

https://docs.microsoft.com/en-us/sql/ssms/agent/sql-server-agent

You can create a JOB which run a DELETE query

Note! I HIGHLY recommend NOT to delete data from the database in most cases! Disk are cheap and you will never know when the history information will be needed, but more important is simply that you should not have a reason for this in most cases. In big system you are using partitions and you can store the old data in separate file as well and there is no need to backup that file every time. In small system you can simply buy another cheap HDD disk if needed. Instead of DELETE rows you should design the system to mark the rows as logically deleted (for example add another column) if needed

CodePudding user response：

1. Build a Stored Procedure with T-SQL that remove your older data than 30 days.
2. Assign procedure to SQL Server Agent.
3. Run server agent every day at specific hours.

With that way, your procedure will run every day and will delete your older data than 30 days according to the Procedure that you write.