Let’s say that you want to clear out your old backup history. Why would you want to do that? Let’s also assume that your maintenance plans (yes, maintenance plans) have not been cleaning up after themselves.
If this has happened over a small period of time, you can just do this:
EXEC sp_delete_backuphistory DATEADD(m, -1, GETDATE());
But what if you have a lot of backup history? Trying to delete two years of backup history will slow things down incredibly on your server and take a very very long time. To the point where your boss will come over and frantically say ‘Hey, why is the server refusing connections?’
Instead you’ll want to use something like this:
DECLARE @oldest_date AS DATETIME;
IF OBJECT_ID('tempdb..#dates') IS NOT NULL
DROP TABLE #dates;
SELECT DISTINCT DATEADD(d, 0, DATEDIFF(d, 0, backup_start_date)) AS d
INTO #dates
FROM dbo.backupset
SELECT @oldest_date = DATEADD(d, 2, MIN(d))
FROM #dates
WHERE d < DATEADD(mm, -1, GETDATE())
EXEC sp_delete_backuphistory @oldest_date;
Why isn’t this in a loop? Because I have a sinus infection and I didn’t feel like being that clever while I’m working from home. That’s why. You probably should be clever and put this in a loop. And put a nice little wait at the end of it that waits for a minute or two before repeating the whole thing. Me? I just sit here and zone out for a few minutes. The point is, this works.
If you’ll excuse me, I’m going to get back to watching day time TV and pressing F5 every 3 minutes.
