declare @days int

set @days = 7 --будут удалены все отправленные уведомления старше 7 дней

declare @continue bit

set @continue = 1

while @continue = 1
BEGIN 
	Begin transaction
		delete top (100) [NotificationAttachment]
		FROM [NotificationAttachment] 
		left join [Notification] on [Notification].Id = [NotificationAttachment].[NotificationId]
		WHERE ([IsSent] IS NOT NULL AND (([SentDate] IS NOT NULL AND [IsSent] = 1) OR [IsSent] = 0)) AND (( [SentDate] IS NOT NULL AND DATEDIFF(day, [SentDate], GETDATE()) > @Days) OR ([SentDate] IS NULL AND DATEDIFF(day, [InsertDate], GETDATE()) > @Days))

	IF @@ERROR <> 0
		IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION

	IF @@TRANCOUNT = 1
	begin
		COMMIT transaction
		set @continue = 
		case when exists (select top 1 [NotificationAttachment].id FROM [NotificationAttachment] left join [Notification] on [Notification].Id = [NotificationAttachment].[NotificationId] WHERE ([IsSent] IS NOT NULL AND (([SentDate] IS NOT NULL AND [IsSent] = 1) OR [IsSent] = 0)) AND (( [SentDate] IS NOT NULL AND DATEDIFF(day, [SentDate], GETDATE()) > @Days) OR ([SentDate] IS NULL AND DATEDIFF(day, [InsertDate], GETDATE()) > @Days)))
			then 1
			else 0
		end
	end else
	begin
		set @continue = 0
	end
END	

set @continue = 1

while @continue = 1
BEGIN 
	Begin transaction
		delete top (100) [Notification]
		FROM [Notification] WHERE ([IsSent] IS NOT NULL AND (([SentDate] IS NOT NULL AND [IsSent] = 1) OR [IsSent] = 0)) AND (( [SentDate] IS NOT NULL AND DATEDIFF(day, [SentDate], GETDATE()) > @Days) OR ([SentDate] IS NULL AND DATEDIFF(day, [InsertDate], GETDATE()) > @Days))

	IF @@ERROR <> 0
		IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION

	IF @@TRANCOUNT = 1
	begin
		COMMIT transaction
		set @continue = 
		case when exists (select top 1 [Notification].id FROM [Notification] WHERE ([IsSent] IS NOT NULL AND (([SentDate] IS NOT NULL AND [IsSent] = 1) OR [IsSent] = 0)) AND (( [SentDate] IS NOT NULL AND DATEDIFF(day, [SentDate], GETDATE()) > @Days) OR ([SentDate] IS NULL AND DATEDIFF(day, [InsertDate], GETDATE()) > @Days)))
			then 1
			else 0
		end
	end else
	begin
		set @continue = 0
	end
END