TFS Optimize database Jobs and their nuances.

Applies to TFS 2017 RTM and Older versions of TFS.

Any job in TFS is a good to run one, which is what keeps the rhythm of the application, Optimize database job has special functions that keeps your databases tidy and updated with Indexes and statistics

Sometimes, you might end up seeing this job running in duplicates and affecting other jobs, for example, when this job is still in progress other jobs may even fail to take off.

Here is a sample error:

[Full Analysis Database Sync]: ---> TF276000: The current Analysis Database Sync job has exited without making any changes. Either another Analysis Database Sync job is already in progress, or the Analysis Database Sync job cannot run because an Optimize Databases job is running. The next Analysis Database Sync job will start at its scheduled time.

This eventually will delay the notifications emanating from TFS event service and you may see delayed TFS emails as well.

When you look at the https://<tfsservername>:8080/tfs/_OI you may notice something like this as well

To make sure we are hitting the right issue, that is duplicate Optimize jobs running, run the below Query, this will eliminate the duplicate jobs running.

Create a new Query against the Tfs_Configuration DB and paste this in, then run it, it should remove the duplicate jobs:

-- Do not modify the below query, do not run it without taking full backup of the database--

DECLARE @jobToSave  UNIQUEIDENTIFIER

DECLARE @jobSource  UNIQUEIDENTIFIER

DECLARE @jobsToDelete TABLE (

Id  UNIQUEIDENTIFIER NOT NULL

)

SELECT  @jobSource = HostId

FROM    tfs_configuration..tbl_ServiceHost

WHERE   ParentHostId IS NULL

INSERT  @jobsToDelete (Id)

SELECT  JobId

FROM    tbl_JobDefinition

WHERE   JobName = 'Optimize Databases'

ORDER BY JobId ASC

DELETE  @jobsToDelete

WHERE   Id = (

SELECT TOP 1

Id

FROM    @jobsToDelete

)

BEGIN TRAN

DELETE  jd

FROM    tbl_JobDefinition jd

JOIN    @jobsToDelete jtd

ON      jtd.Id = jd.JobId

DELETE  js

FROM    tbl_JobSchedule js

JOIN    @jobsToDelete jtd

ON      jtd.Id = js.JobId

DELETE  jq

FROM    tfs_configuration..tbl_JobQueue jq

JOIN    @jobsToDelete jtd

ON      jtd.Id = jq.JobId

AND jq.JobSource = @jobSource

COMMIT TRAN

You will see results as below:

Cheers!

Content created by – Vimal Thiagaraj