TLDR – The Add-SPShellAdmin and SPWebApplication.GrantAccessToProcessIdentity are very similar in what they do, but there are a few key differences: Add-SPShellAdmin: Should be used for granting admin accounts access to...
Weird/crazy issue recently. Subscriptions were getting bogged down/failing in a SSRS SharePoint Integrated Mode environment. This issue seemed sporadic..some days subscriptions would fire off at the scheduled times and sometimes they’d get stuck processing for a few hours before users would receive the subscriptions. After some troubleshooting/digging into this issue we noticed something off. We ran a query in SQL to compare the SSRS Subscriptions with the SQL Agent Jobs on the server. These numbers did not match..There were around 70 additional SQL Agent Jobs on the server..and they were not attached to a subscription. Opening one of them up shows that it was a SQL Agent Job created by SSRS for a subscription..but no subscription was associated. Disabling these jobs fixed the issues.
Here’s the SQL script (This renames all SQL Agent Jobs to have a prefix of ZZZZ_ and disables the job):
CREATE TABLE #jobsToRename (RowID int IDENTITY(1,1),
; WITH PotentiallyOrphanedJobs AS
from msdb.dbo.sysjobs j
WHERE j.Name NOT IN (SELECT CONVERT(nvarchar(128), Schedule.ScheduleID)
FROM ReportServer.dbo.ReportSchedule INNER JOIN
ReportServer.dbo.Schedule ON ReportSchedule.ScheduleID = Schedule.ScheduleID INNER JOIN
ReportServer.dbo.Subscriptions ON ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID INNER JOIN
ReportServer.dbo.[Catalog] ON ReportSchedule.ReportID = [Catalog].ItemID AND Subscriptions.Report_OID = [Catalog].ItemID)
AND j.[description] LIKE 'This job is owned by a report server process.%'
AND j.name NOT LIKE 'ZZZZ%' -- in case you have renamed some orphaned jobs in the past
FROM PotentiallyOrphanedJobs j
JOIN msdb.dbo.sysjobsteps js
ON j.job_id = js.job_id
WHERE js.command LIKE '%TimedSubscription%'
--INSERT INTO #jobsToRename(JobName) --Uncomment this row if you want to do the rename process
FROM TimedSubscriptionJobs t
JOIN msdb.dbo.sysjobs j
ON t.job_id = j.job_ID
/* --Remove this comment marker if you want to rename the orphaned jobs
DECLARE @RowCounter int, @RowCount int, @CurrentJobName nvarchar(128), @NewName nvarchar(128)
SELECT @RowCount = COUNT(*), @RowCounter = 1
WHILE @RowCounter <= @RowCount
SELECT @CurrentJobName = JobName,
@NewName = 'ZZZZ_' + JobName
WHERE RowID = @RowCounter
EXEC msdb.dbo.sp_update_job @job_name = @CurrentJobName,
@new_name = @NewName,
@enabled = 0
SELECT @RowCounter += 1
*/ --Remove this comment marker if you want to rename the orphaned jobs