As databases grow, keeping every historical record in the production database can affect storage, query performance, backup size, and maintenance. A practical solution is to automatically archive old data and then delete it from the production database based on a defined retention policy.
For example, an organization may decide to keep the last 2 years of data in the production database while moving older records to an archive database.
What Is Database Archiving?
Database archiving is the process of moving older or less frequently accessed records from the primary production database to a separate archive table or database.
A typical process looks like:
│
│ Records older than retention period
▼
Archive Database
│
│ Validate archived records
▼
Delete from Production
│
▼
Audit & Monitoring
This approach allows the production database to remain smaller while historical information remains available when required.
Example Scenario
Suppose we have an Orders table:
(
OrderId INT PRIMARY KEY,
CustomerId INT,
OrderDate DATETIME,
Amount DECIMAL(18,2)
);
The business requirement is:
Keep the last 2 years of orders in the production database and archive older orders.
Step 1: Create an Archive Table
The archive table can have the same structure as the production table.
INTO Orders_Archive
FROM Orders
WHERE 1 = 0;
This creates an empty archive table with the same columns.
For production systems, it is usually better to explicitly define the archive table and its indexes rather than relying only on SELECT INTO.
Step 2: Identify Records for Archiving
The following query identifies records older than two years:
FROM Orders
WHERE OrderDate < DATEADD(YEAR, -2, GETDATE());
The retention period should ideally be configurable rather than hard-coded.
Step 3: Move Data to the Archive
A basic implementation could use:
SELECT *
FROM Orders
WHERE OrderDate < DATEADD(YEAR, -2, GETDATE());
After the archive operation completes successfully, the records can be removed from the production table.
WHERE OrderDate < DATEADD(YEAR, -2, GETDATE());
However, production systems should not blindly execute these statements independently.
The archive and delete operation should include validation, transaction handling, logging, and duplicate protection.
Step 4: Use Batch Processing
If the table contains millions of records, deleting everything in one transaction can cause excessive locking and transaction-log growth.
Instead, delete records in batches:
BEGIN
DELETE TOP (5000)
FROM Orders
WHERE OrderDate < DATEADD(YEAR, -2, GETDATE());
IF @@ROWCOUNT = 0
BREAK;
END
Batch processing reduces the impact on the production database.
Step 5: Add an Audit Table
An archive process should maintain an audit trail.
For example:
(
Id INT IDENTITY PRIMARY KEY,
TableName VARCHAR(100),
ArchivedRows INT,
ArchiveDate DATETIME,
Status VARCHAR(20),
ErrorMessage VARCHAR(1000)
);
The application or stored procedure can record:
- Table name
- Number of records archived
- Execution date
- Status
- Error information
This makes the automated process easier to monitor and troubleshoot.
Automating the Process
The archive process can be scheduled using different technologies depending on the environment.
SQL Server Agent
For traditional SQL Server environments, SQL Server Agent can execute a stored procedure automatically.
Example:
│
└── Every night at 2:00 AM
│
▼
Archive Stored Procedure
│
┌──────┴──────┐
▼ ▼
Archive Data Delete Data
Azure Function
For Azure-based applications, an Azure Function with a Timer Trigger can execute the archive process on a schedule.
Example:
│
│ Timer Trigger
▼
Archive Stored Procedure
│
├── Archive old records
├── Validate archive
├── Delete archived records
└── Write audit log
A timer expression could be configured to execute the function every day.
0 0 2 * * *
This represents a scheduled execution at approximately 2:00 AM UTC.
Hangfire in .NET
If the application already uses Hangfire, a recurring job can also be used:
"database-archive",
() => ArchiveOldData(),
Cron.Daily(2)
);
The job can call a stored procedure responsible for the archive operation.
Best Practices
Before implementing automatic deletion, consider the following:
1. Define a Retention Policy
Clearly determine how long data should remain in the production database.
For example:
2–7 years → Archive Database
7+ years → Eligible for permanent deletion
The actual retention period should be based on business, contractual, regulatory, and legal requirements.
2. Archive Before Delete
Always ensure that the archive operation has completed successfully before deleting production data.
3. Use Batch Processing
For large tables, process records in manageable batches instead of deleting millions of rows at once.
4. Create Proper Indexes
If records are selected using:
WHERE OrderDate < ...
an appropriate index on OrderDate can significantly improve the archive operation.
5. Monitor the Job
The automated process should provide visibility into:
- Last successful execution
- Number of records archived
- Number of records deleted
- Execution duration
- Failures
- Exceptions
6. Test Recovery
An archive system is only useful if archived data can actually be retrieved when required. Periodically test the recovery process.
Archive vs. Delete
Archiving and deleting are different operations.
Archive Preserve historical data
Delete Permanently remove data
Backup Protect against data loss
Retention Define how long data should be maintained
An archive should not be considered a replacement for database backups.
Conclusion
Automating database archiving and deletion is an effective way to control database growth and maintain production performance.
A well-designed solution should follow this sequence:
↓
Archive Data
↓
Validate Archive
↓
Delete from Production
↓
Record Audit Information
↓
Monitor & Alert
For .NET and Azure applications, a combination of Azure Functions, SQL stored procedures, batch processing, audit logging, and monitoring can provide a reliable automated database lifecycle management solution.
The key principle is simple: archive first, validate successfully, and only then delete from the production database.
.gif)
0 Comments