April 27, 2011 at 9:37 am
When creating backup jobs in SQL 2008, I script the backup jobs from another 2008 Instance running same build.
Initially upon check, everything looks good. Schedules are OK. I make any necessary changes to the Device names & databases that need backing up within the new jobs that were just scripted out on new Instance. However next day, I notice all schedules for all jobs have changed and are identical. This only happens when I script jobs in 2008. Any idea why?
April 27, 2011 at 9:50 am
Not sure, I'd have to see the scripts that were run and what modifications you made and how you made them. Are you sure you were connected to the correct server?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 2, 2011 at 1:57 pm
I've experienced this before, but I'm not quite sure why this happens either.
I do have a theory though. In the job script, there is code like in sample below:
...
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'daily',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20110321,
@active_end_date=99991231,
@active_start_time=210000,
@active_end_time=235959,
@schedule_uid=N'40c82ca4-8b21-4fc6-ace7-658669daeb0d'
...
Could it be that the @schedule_uid from the last line of above sample accidentally coincides with that of another (already existing) job schedule, and, as a result, overwrites the existing schedules?
Just a thought...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 2, 2011 at 2:04 pm
I have actually just tested the above scenario, and was able to reproduce one job schedule overwriting another, by simply using the same @schedule_uid.
I deployed a new job on a server using an existing @schedule_uid, but different exec time and different schedule name.
After deploying, all pre-existing jobs with the same @schedule_uid as that in the newly deployed job had the same exec time (and schedule name) as the newly deployed job.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 2, 2011 at 2:07 pm
Are you scripting out jobs created by maintenance plans?
I have come across an issue in 2008R2 RTM where if two jobs have steps with the same name it confuses the schedules for them and updates incorrectly.
---------------------------------------------------------------------
May 2, 2011 at 6:35 pm
I have that experience too. What I usually do is when I execute the scripts on the target, I usually remove the @schedule_uid and will edit the schedules appropriately. That's lot of work.
Actually @schedule_uid is different for SQL 2005 and 2000. When you script the Jobs in higher version and try to create them on lower version you get this issue too.
Thank You,
Best Regards,
SQLBuddy.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply