2010-02-08

SQL Server Agent schedule owner

When a database mirroring session is created using Microsoft SQL Server Management Studio (SSMS) the job "Database Mirroring Monitor Job" is created automatic. But if you create the database mirroring session by script the job is not created. This is described in more details tin the MSDN Library article "Monitoring Database Mirroring".

A database instance had a mirroring session running against one host, and the mirroring host was recycled. Then the database mirroring sessions were recreated against another host. This time by script.
No Problem I thought.
The job "Database Mirroring Monitor Job" was scripted out in SSMS and then created on the mirroring database instance. It then turns out that the jobs was not created exact as on the principal database instance.

The owner of the SQL Agent schedule "Database Mirroring Monitor Schedule" was NULL on the mirroring installation where is was "sa" on the principal installation.
This does not show in SSMS windows or by calling the procedure msdb.dbo.sp_help_schedule. But the table msdb.dbo.sysschedules holds the SID of the schedule owner.
SELECT [msdb].[dbo].[sysschedules].[name],[msdb].[dbo].[sysschedules].[owner_sid]
FROM msdb.dbo.[sysschedules]
WHERE [msdb].[dbo].[sysschedules].[name]=N'Database Mirroring Monitor Schedule';

I could not find a way to alter the owner of a given schedule in SSMS. The procedure msdb.dbo.sp_update_schedule does the trick.
EXECUTE [msdb].[dbo].[sp_update_schedule]
@name = N'Database Mirroring Monitor Schedule',
@owner_login_name = N'sa';

You could also do it quick and dirty.
UPDATE [msdb].[dbo].[sysschedules]
SET [owner_sid]=0x01
WHERE [sysschedules].[name]=N'Database Mirroring Monitor Schedule';
Not nice!

Now the schedules are identical and I can disregard the finding.

No comments: