declare @Days int

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

declare @continue bit

set @continue = 1

while @continue = 1
BEGIN 
	Begin transaction
		delete top (100) [ImportMailMessageFile]
		FROM [ImportMailMessageFile] 
		left join [ImportMails] on [ImportMailMessageFile].Id = [ImportMails].MailFileId
		where /*[ImportMails].TaskId is NULL  AND*/ DATEDIFF(day, [ImportMails].[Date], GETDATE()) > @Days	and [ImportMails].[ImapMailId] is not null

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

	IF @@TRANCOUNT = 1
	begin
		COMMIT transaction
		set @continue = 
		case when exists (select top 1 [ImportMailMessageFile].id FROM [ImportMailMessageFile] 	left join [ImportMails] on [ImportMailMessageFile].Id = [ImportMails].MailFileId where /*[ImportMails].TaskId is NULL  AND*/ DATEDIFF(day, [ImportMails].[Date], GETDATE()) > @Days	and [ImportMails].[ImapMailId] is not null)
			then 1
			else 0
		end
	end else
	begin
		set @continue = 0
	end
END
	
set @continue = 1

--для поп3 в ImportMails зануляем поле MailFileId
while @continue = 1
BEGIN 
	Begin TRANSACTION 
		update top (100) [ImportMails]
		SET MailFileId = null
		WHERE /*TaskId is NULL  AND */DATEDIFF(day, [Date], GETDATE()) > @Days	and [Serial] is not null
		
	IF @@ERROR <> 0
		IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
		
	if @@TRANCOUNT = 1
	begin
		COMMIT TRANSACTION
		set @continue = 
		case when exists (select top 1 [ImportMails].id from [ImportMails] where MailFileId IS NOT null /*AND TaskId is NULL */ AND DATEDIFF(day, [Date], GETDATE()) > @Days	and [Serial] is not null)
			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) [ImportMailMessageFile] 
		FROM [ImportMailMessageFile]  LEFT JOIN [ImportMails] on [ImportMailMessageFile].id = ImportMails.MailFileId
		Where [ImportMails].id  is NULL

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

	if @@trancount = 1
	begin
		COMMIT transaction
		set @continue =
		case when exists (select top 1 ImportMailMessageFile.id from [ImportMailMessageFile] LEFT JOIN [ImportMails] on [ImportMailMessageFile].id = ImportMails.MailFileId Where [ImportMails].id  is NULL )
			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) [ImportMails]
		where /*[ImportMails].TaskId is NULL  AND*/ DATEDIFF(day, [ImportMails].[Date], GETDATE()) > @Days	and [ImportMails].[ImapMailId] is not null

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

	IF @@TRANCOUNT = 1
	begin
		COMMIT transaction
		set @continue = 
		case when exists (select top 1 [ImportMails].id FROM [ImportMails] where /*[ImportMails].TaskId is NULL  AND*/ DATEDIFF(day, [ImportMails].[Date], GETDATE()) > @Days	and [ImportMails].[ImapMailId] is not null)
			then 1
			else 0
		end
	end else
	begin
		set @continue = 0
	end
END