When MS_SSISServerCleanupJobLogin Is Orphaned

Sreekanth Bandarla noticed a problem in cleaning up SSIS metadata:

Couple of weeks ago I was analyzing a server for space and noticed SSISDB database was abnormally huge (this Instance was running just a handful of packages). I noticed couple of internal schema tables in SSISDB were huge (with some hundreds of millions of rows), well that’s not right. There should be SSIS Server maintenance job which SQL server creates to purge older entries based on the retention settings right? My immediate action was to check the retention period set and what’s the status of the job.  As I suspected, the job was failing (looks like this has been failing since ages) with below error.

The job failed.  The Job was invoked by Schedule 9 (SSISDB Scheduler).  The last step to run was step 1 (SSIS Server Operation Records Maintenance).
Execute as Login failed for the requested login ‘##MS_SSISServerCleanupJobLogin##’

Read on for the root cause and solution.

Related Posts

SSIS Error “Deserializing The Package”

Andy Leonard troubleshoots an odd error in SSIS: Exception deserializing the package “Operation is not valid due to the current state of the object.”. (Microsoft.DataTransformationServices.VsIntegration) As a professional consultant who has been blogging about SSIS for 12 years and authored and co-authored a dozen books related to Microsoft data technologies, my first response was:“Whut?!” That […]

Read More

Effective Identities And Power BI Embedded

Angela Henry shows how you can use Power BI Embedded for row-level security even when the accessing users don’t have Power BI accounts: Now that you familiar with Row Level Security in Power BI, how do you make it work when you want to pass in your customer’s identifier because your customers don’t have Power […]

Read More

Categories

August 2018
MTWTFSS
« Jul Sep »
 12345
6789101112
13141516171819
20212223242526
2728293031