Cannot delete jobs created from MSX

Question

Cannot delete jobs created from MSX

AlexSQLForums

SSChampion

Points: 14277
More actions
June 19, 2006 at 8:03 am

#61249

There are few jobs on my server created from MSX server.
They are failing because the databases were deleted.
I cannot find this MSX server cause i think it was also deleted.
When i go to Job system tab in SQL Agent properties it says None
for MSX server.
How can i get rid of these jobs?
Thanks

Alex S

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Nali SSCommitted Points: 1525 More actions · Answer 1

Have you tried defecting from the MSX server:

Right click on SQL Server Agent (under Management) in Enterprise Manager, then choose Multi-Server Administration, then click on Defect from MSX.

EdVassie SSC Guru Points: 60478 More actions · Answer 2

If all else fails, run

UPDATE msdb..sysjobs SET originating_server = @@SERVERNAME

You can now manage the jobs locally

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

AlexSQLForums SSChampion Points: 14277 More actions · Answer 3

There is no such option under multi-server admin.

I only see make target make master.

The master server was deleted. so maybe that's why it's not showing up.

Alex S

Nali SSCommitted Points: 1525 More actions · Answer 4

I would then try Ed's suggestion above. If that doesn't work, I guess I would create another MSX/Master server, try to join this one to it, and then try to delete the jobs, but Ed's script should work.......

AlexSQLForums SSChampion Points: 14277 More actions · Answer 5

Thanks for the statement.

When i try to delete jobs it still says cannot delete blah blah from MSX server.

If i delete these jobs from sysjobs will that work?

Alex S

philcart SSC-Forever Points: 47796 More actions · Answer 6

Two other options you can try,

1) Generate a script of the jobs. In the resulting script, make sure the line that executes 'sp_add_jobserver' has '(local)' as the target server. Then run the script in Query Analyser.

2) The more destructive of the two. Run the following, msdb.dbo.sp_msx_defect @forced_defection = 1. This will forcibly defect the target server and remove the scheduled jobs.

--------------------
Colt 45 - the original point and click interface

AlexSQLForums SSChampion Points: 14277 More actions · Answer 7

AlexSQLForums

SSChampion

Points: 14277

June 26, 2006 at 9:41 am

#645864

Thanks Phil it worked.

Alex S

Balmukund SSCertifiable Points: 6648 More actions · Answer 8

Balmukund

SSCertifiable

Points: 6648

June 27, 2006 at 1:21 am

#645984

http://support.microsoft.com/?id=281642

dharper3 SSCrazy Points: 2391 More actions · Answer 9

Hi all, I had a similar problem recently after I performed a rename of the server, newly created jobs were ok, all old jobs wouldn't let me modify or delete them. Thanks to EdVassie post above worked perfectly, was then able to delete the jobs.

Thanks again.