Friday, November 22, 2019

Deleting a SQL Agent Job originated from an MSX server

Hit very weird situation when tried to remove jobs running on remote-target servers in multi-server managed environment.

First what I've tried is to unassign target servers from multi-server job on MSX server, however, it did not delete jobs on some targets.

When I've tried to delete them manually via SSMS I've got following error:

TITLE: Microsoft SQL Server Management Studio
------------------------------
Drop failed for Job 'CPU_Usage_By_Database'.  (Microsoft.SqlServer.Smo)
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=15.0.18131.0+((SSMS_Rel).190606-1032)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Job&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server. (Microsoft SQL Server, Error: 14274)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.5223&EvtSrc=MSSQLServer&EvtID=14274&LinkId=20476
------------------------------

When I tried to follow help links, provided by newest SSMS, they led me to Microsoft's advertisement portal.

The second I've tried was manual deletion of that job by a script:

EXEC msdb.dbo.sp_delete_job @job_name=N'My_MSX_Created_Job', @delete_unused_schedule=1;

It was not successful and gave me following short error without Microsoft adds:
Msg 14274, Level 16, State 1, Procedure sp_delete_job, Line 91 [Batch Start Line 59]

Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server.

I've looked in "sysjobs" table and saw there a column "originating_server_id", which is referencing to "sysoriginatingservers_view" view, which has "originating_server_id" value always to be zero for the local server.
So the fixing script is as easy as this:
UPDATE msdb..sysjobs
SET originating_server_id = 0
WHERE name=N'<Your Job Name>';
GO
EXEC msdb.dbo.sp_delete_job
       @job_name=N'<Your Job Name>',
       @delete_unused_schedule=1;

GO


No comments:

Post a Comment