Applying Data Retention to a SQL Table

In SQL Server, there’s often a need to delete old data from a database table. This could be for reasons like adhering to GDPR policies, managing oversized tables that are slowing down operations, staying within SQL Server Express’s 10GB limit, or simply cleaning up application logs and errors. Whether it’s a one-time task or a regular maintenance activity, how you approach it can vary depending on your needs. Here’s a straightforward guide to help you handle it.

Deleting Data in SQL Server

1. Set Up a Test Database
Always work on a test database first. Restore a backup copy of your production database to your SQL Server for safe testing.

2. Identify a Large Table
Pick a table that has a lot of data. This is where you’ll focus your deletions.

3. Count Rows by Date
Run a query to count rows grouped by date. Save this data into a temporary table to track your progress during the deletion process.

SELECT COUNT(*) AS RowCount, CAST(DateColumn AS DATE) AS [Date]  
INTO #TempRowCounts  
FROM YourTable  
GROUP BY CAST(DateColumn AS DATE)  

4. Perform Deletions in Batches
Avoid deleting large volumes of data in one go, as this can lock the table and cause performance issues. Use batching to delete smaller chunks of data.

Batch Deletion Example:

DECLARE @BatchSize INT = 1000  
WHILE EXISTS (SELECT 1 FROM YourTable WHERE DateColumn < '2012-01-01')  
BEGIN  
    DELETE TOP (@BatchSize)  
    FROM YourTable  
    WHERE DateColumn < '2012-01-01'  

    -- Optional delay to reduce server load  
    -- Remove the space after the first char of the wait command below
    -- I have to do this way to publish else it throws a JSON error :) 
    W AITFOR DELAY '00:00:10'  
END  

5. Verify Results
After each batch or the entire deletion process, check the row counts again. Compare them with your initial counts to ensure the data was removed as expected.

Looking good – All data on orders beyond 7 years (< 1 January 2012 00:00) has been deleted.

6. Schedule Maintenance.
Once you’ve cleaned up the table, you can set up a SQL Agent job to maintain it regularly. For example, you could schedule monthly deletions to keep the table within your data retention policies.

Additional Tips

> Index Maintenance: After large deletions, rebuild or reorganize indexes to maintain query performance.
> Transaction Logs: Be aware that deleting large amounts of data can bloat your transaction logs. Monitor log size and back them up as needed.
> Testing First: Always test queries in a non-production environment to avoid accidental data loss.
> Off-Peak Hours: Schedule deletions during low-traffic times to minimize impact on users.
> Retention Policies: Confirm your organization’s data retention requirements before deciding what to delete.

These steps and tips give you a simple way to manage data retention. Adjust your approach as needed to fit your database’s requirements.