Pass ... but IME it works better doing it in a loop in reasonable sized batches.
We get all the PKeys into a #temporary table, which also has an IDENTITY column, ordered by the clustered index on the underlying table (so we delete sympathetically with that index), and then delete in batches based on the IDENTITY by joining the #tempTable (on PKey values). We delete about 10M rows each day (i.e. that's the table with the largest daily stale-data purge) using this method.
Perhaps something else is being logged - lots of indexes on the underlying table being purged? Whatever it is it is being logged to ensure that SQL can roll the transaction back if necessary, giving SQL smaller transactions to work with makes that job a lot smaller ./ easier. If you are running in SIMPLE Recovery Model then each batch will checkpoint the DB and the log space will be reused for each successive batch (more-or-less)
Using
WHERE Year(PLT_Date) < Year(GetDate())-3
is likely to slow the operation (although not responsible for the size of the log file!) and cause SQL not to use any index on PLT_Date column.
I would use something like this (untested) :
DECLARE @dtCutoff datetime = DATEADD(Year, DATEDIFF(Year, 0, GetDate())-3, 0)
--
SELECT [T_ID]=IDENTITY(int, 1, 1) PKey1, PKey2, ...
INTO #TEMP
FROM PLT
WHERE PLT_Date < @dtCutoff
ORDER BY ClusteredIndexKey1, ClusteredIndexKey2, ...
--
DECLARE @intBatchSize int = 10000,
@intOffset int = 1,
@dtStart datetime,
@intRowCount int = 1 -- Force first iteration
--
WHILE @intRowCount > 0
BEGIN
SELECT @dtStart = GetDate() -- Start time, used to calculate duration
--
DELETE P
FROM #TEMP AS T
JOIN PLT AS P
ON P.PKey1 = T.PKey1
AND P.PKey2 = T.PKey2
...
WHERE T.T_ID BETWEEN @intOffset AND @intOffset + @intBatchSize
SELECT @intRowCount = @@ROWCOUNT
--
SELECT @intOffset = @intOffset + @intBatchSize
--
PRINT 'Deleted: ' + CONVERT(varchar(20), @intRowCount) + ', Duration: ' + CONVERT(varchar(20), DATEDIFF(Second, @dtStart, GetDate())) + ' seconds, remaining to delete=' + CONVERT(varchar(20), @intRowsToDelete)
RAISERROR (N'Waitfor', 10, 1) WITH NOWAIT -- Messages display may be delayed without this
WAITFOR DELAY '00:00:03' -- Optional pause to release BLOCK on any other processes
END
If database is in FULL Recovery Model make sure Log Backups are running every minute, until the delete operation is completed.
