locked
Warehouse Optimize Databases job runs at wrong time

    Question

  • Hello,

    thanks to the great Administrative Report Pack for TFS 2010, we found that that Optimize Databases job runs every day at 2am, blocking the Full Analysis Data Sync job from running (also scheduled for 2am). As Grant Holliday wrote in his post about the report pack that the Optimize Databases job runs by default at 1am, I wonder why it is running now at 2am every day.

    I could not find a way how to configure the start time of the Optimize Databases job via the WarehouseControlWebService. Any ideas how to set it back to 1am, so that it does not interfere with the Full Analysis Data Sync job?

    Thank you very much,
    Matthias Huber

    Friday, September 03, 2010 12:32 PM

Answers

  • Hi Matthias,

    1 Run following query in TFS_Configuration Database to check the schedule for Optimize Databases job. Some time, there mey be more that one Optimize Databases jobs

    SELECT     tbl_JobSchedule.JobId, tbl_JobSchedule.ScheduledTime, tbl_JobSchedule.Interval, tbl_JobSchedule.TimeZoneId, tbl_JobDefinition.JobName
    FROM         tbl_JobDefinition RIGHT OUTER JOIN
                          tbl_JobSchedule ON tbl_JobDefinition.JobId = tbl_JobSchedule.JobId
                          where JobName='Optimize Databases'

    By default, The Interval is 86400 seconds which means one day, so the run time depends on ScheduledTime.

    2 Edit the schedule by TFS API. (You can edit that in Database, but it is not suggested)

          Microsoft.TeamFoundation.Client.TfsConfigurationServer server = new Microsoft.TeamFoundation.Client.TfsConfigurationServer(new Uri("http://Server:8080/tfs"));
          var jobService = server.GetService(typeof(ITeamFoundationJobService)) as ITeamFoundationJobService;     
          var jobs = jobService.QueryJobs();
    
          bool exist = false;
          foreach (var job in jobs)
          {
            if (job.Name == "Optimize Databases")
            {
    //Only one job is necessary
              if (exist)
              {
                jobService.DeleteJob(job);
                continue;
              }
    
              var OptimizeDatabasesJob = job;
              OptimizeDatabasesJob.Schedule.Clear();
    
              TeamFoundationJobSchedule schedule = new TeamFoundationJobSchedule(DateTime.Now.Date.AddHours(1), 86400, TimeZoneInfo.Utc);
              OptimizeDatabasesJob.Schedule.Add(schedule);
    
              jobService.UpdateJob(OptimizeDatabasesJob);
              exist = true;
            }
          } 
    

     


    Best regards,
    Ruiz
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg @ microsoft.com
    • Marked as answer by Matthias Huber Thursday, September 09, 2010 2:09 PM
    Monday, September 06, 2010 6:57 AM
    Moderator

All replies

  • Hi Matthias,

    1 Run following query in TFS_Configuration Database to check the schedule for Optimize Databases job. Some time, there mey be more that one Optimize Databases jobs

    SELECT     tbl_JobSchedule.JobId, tbl_JobSchedule.ScheduledTime, tbl_JobSchedule.Interval, tbl_JobSchedule.TimeZoneId, tbl_JobDefinition.JobName
    FROM         tbl_JobDefinition RIGHT OUTER JOIN
                          tbl_JobSchedule ON tbl_JobDefinition.JobId = tbl_JobSchedule.JobId
                          where JobName='Optimize Databases'

    By default, The Interval is 86400 seconds which means one day, so the run time depends on ScheduledTime.

    2 Edit the schedule by TFS API. (You can edit that in Database, but it is not suggested)

          Microsoft.TeamFoundation.Client.TfsConfigurationServer server = new Microsoft.TeamFoundation.Client.TfsConfigurationServer(new Uri("http://Server:8080/tfs"));
          var jobService = server.GetService(typeof(ITeamFoundationJobService)) as ITeamFoundationJobService;     
          var jobs = jobService.QueryJobs();
    
          bool exist = false;
          foreach (var job in jobs)
          {
            if (job.Name == "Optimize Databases")
            {
    //Only one job is necessary
              if (exist)
              {
                jobService.DeleteJob(job);
                continue;
              }
    
              var OptimizeDatabasesJob = job;
              OptimizeDatabasesJob.Schedule.Clear();
    
              TeamFoundationJobSchedule schedule = new TeamFoundationJobSchedule(DateTime.Now.Date.AddHours(1), 86400, TimeZoneInfo.Utc);
              OptimizeDatabasesJob.Schedule.Add(schedule);
    
              jobService.UpdateJob(OptimizeDatabasesJob);
              exist = true;
            }
          } 
    

     


    Best regards,
    Ruiz
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg @ microsoft.com
    • Marked as answer by Matthias Huber Thursday, September 09, 2010 2:09 PM
    Monday, September 06, 2010 6:57 AM
    Moderator
  • Thank you very much, this did the trick!

    Best regards,
    Matthias Huber

    Thursday, September 09, 2010 2:10 PM