INSERT INTO... SELECT over a LinkedServer in smaller batches
You could split them up in batches inside the dynamic SQL statement. 

An example applied to your insert statement, per 1000 rows with the use of OFFSET ... FETCH :

EXECUTE (

'
DECLARE @RowsProcessed int = 0;
DECLARE @Batch int = 1000;
DECLARE @rowcount int = 1;

WHILE @rowcount!= 0
BEGIN

'INSERT INTO dbo.Notifications
(
    [Subject], 
    [Body], 
    [PriorityId], 
    [StartDate], 
    [EndDate], 
    [IsActive], 
    [UserId],
    [SourceNotificationId]
)
SELECT 
    [Subject], 
    [Body], 
    [PriorityId], 
    [StartDate], 
    [EndDate], 
    n.[IsActive], 
    [DS.DataMigration].[Migration].[ufnGetNewUserId](n.[UserId]),
    [NotificationId]

FROM ['+ @LinkedServer +'].['+@DatabaseName+'].[dbo].[Notifications] n
    JOIN ['+ @LinkedServer +'].['+@DatabaseName+'].[dbo].[Users] u ON n.UserId = u.UserId 
    JOIN ['+ @LinkedServer +'].['+@DatabaseName+'].[dbo].[Applications] a ON u.ApplicationId = a.ApplicationId
WHERE A.ApplicationURL = ''' + @ApplicationURL + '''
ORDER BY n.NotificationId 
OFFSET @RowsProcessed ROWS 
FETCH NEXT @Batch ROWS ONLY;

SET @rowcount = @@ROWCOUNT;

SET @RowsProcessed += @Batch;
END
')


Note that you have to add an ORDER BY with the OFFSET ... FETCH. I have chosen the NotificationId  field of the dbo.notifications table. You could change this if needed.

Here is a smaller working db&lt;>Fiddle example.