April 9, 2014 at 10:18 am
i have setup the MDW database for collecting stats on my production server. I have enabled the data collector to collet the stats for stored proc usage analysis and table usage analysis. For some reason the collection jobs are failing throwing the error --
Package "Set_{3006169E-D33A-4101-A1DE-9F0DABF7D84E}_Master_Package_Collection" failed. sa is disabled on our server. I probably think the packages are getting created with owner sa and as sa is disabled they are not able to collect stats. how to overcome this issue?
November 6, 2015 at 1:52 pm
This looks old, but I had a similar situation. This is how I fixed it.
--Step 1: Check SQL Agent Job Ownership
--For SQL 2008-2012
SELECT NAME
,description
,SUSER_SNAME(ownersid)
--,*
FROM msdb.dbo.sysssispackages -- sql 2008
WHERE SUSER_SNAME(ownersid) <> 'sa'
--For SQL 2005
/*
SELECT name
,description
,SUSER_SNAME(ownersid)
FROM msdb.dbo.sysdtspackages90 -- sql 2005
*/
--Step 2: Change the owners of the SQL Agent
--Caution, be sure you you know which jobs you want to change ownership on. This script will change ALL SQL agent jobs that have the
--"OldDomain\UserAccount or sa" you enter. Use additional indentifiers if needed. All of my jobs use the SQL Agent service account.
--For SQL 2008-2012
UPDATE msdb.dbo.sysssispackages
SET ownersid = SUSER_SID('NewDomain\UserAccount') --Set new account (normally the SQL Agent Service Account)
WHERE SUSER_SNAME(ownersid) IN ('OldDomain\UserAccount or sa') --This will be the 'result' from Step 1 above
--AND [name] = 'MaintenancePlanNameHere' ;
--For SQL 2005
/*
UPDATE msdb.dbo.sysdtspackages90
SET ownersid = SUSER_SID('NewDomain\UserAccount') --Set new account (normally the SQL Agent Service Account)
WHERE SUSER_SNAME(ownersid) IN ('OldDomain\UserAccount or sa') --This will be the 'result' from Step 1 above
--AND [name] = 'MaintenancePlanNameHere' ;
*/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply