CODEWITHSUNDEEP
c#sqlsql-serverasp.net-mvc
Eduardo Henrique
Eduardo Henrique

Reputation: 49

How to automatically delete old records from sql server database?

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?

Upvotes: 0

Views: 5771

Answers (2)

Ronen Ariely
Ronen Ariely

Reputation: 2434

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://learn.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

Update: and what if we are using SQL Server Express?!?

SQL Server express does not support the use of the SQL Server Agent, which means that we need a different solution to schedule task, and the common solutions you can use are:

Solution 1: Using external scheduler

Disadvantage: This solution is in the Operating System level

If you are using SQL Server on windows, then you can Windows Task Scheduler: start menu -> search for "Task Scheduler" -> Right click "Task Scheduler" and select "Create Basic Task" -> fill the task information -> choose "Start a Program" as the action type and execute a ".bat" file which include sqlcmd command which execute your query

If you are using Linux then you can use Cron daemon - a system-managed executable that runs in memory in the background with which users may schedule tasks.

Solution 2: schedule tasks using SQL Server SERVICE (service broker); CONVERSATION TIMER; MESSAGE; CONTRACT; QUEUE

Note! This is my preferred option and I use it even when Agent is supported for many reasons! Service broker is super reliable, it's part of the SQL Engine, there is almost no overhead, it allows asynchronous and timed (repeated) execution at any time interval. With that said, it does not have access to the operating system like the SQL Agent does.

You can get a full sample including all the code to implement this solution in this post: https://www.sqlteam.com/articles/scheduling-jobs-in-sql-server-express-2

Upvotes: 1

Eren Temelli
Eren Temelli

Reputation: 333

  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.

Upvotes: 1

Related Questions