0

I have two Production servers, one active, one passive. On the active server, application specific jobs are enabled and run all the time. Jobs on the passive server do not run.

On a regular schedule, we fail over between the servers to test our fail over process. Once done, we remain on the "new" server until the next scheduled fail over.

We have a custom service built that logs job statuses to splunk utilizing the InstanceID from sys.jobhistory. Problem is, when we fail over, the InstanceID on the passive node is MUCH smaller than the active one, and the service is checking for "max(InstanceID)" to know what record in history to begin pulling data from. My questions is around reseeding the identity column in sys.jobhistory as part of the fail over process to be max(id) from prod server + 1. In theory this should work, but I'm concerned with any potential issues.

1
  • 2
    isn't better to add to the splunk log the instancename so you can also track down where the jobs run?
    – MBuschi
    Jun 16, 2021 at 9:04

1 Answer 1

0

It seems only stored procedures have dependencies on that table, so you won't break any FK reference by reseeding that ID.

dependencies sysjobhistory

Also, if you generate the script of dbo.sysjobhistory, you'll see it's not even a PK.

CREATE TABLE [dbo].[sysjobhistory](
    [instance_id] [int] IDENTITY(1,1) NOT NULL,
    [job_id] [uniqueidentifier] NOT NULL,
    [step_id] [int] NOT NULL,
    [step_name] [sysname] NOT NULL,
    [sql_message_id] [int] NOT NULL,
    [sql_severity] [int] NOT NULL,
    [message] [nvarchar](4000) NULL,
    [run_status] [int] NOT NULL,
    [run_date] [int] NOT NULL,
    [run_time] [int] NOT NULL,
    [run_duration] [int] NOT NULL,
    [operator_id_emailed] [int] NOT NULL,
    [operator_id_netsent] [int] NOT NULL,
    [operator_id_paged] [int] NOT NULL,
    [retries_attempted] [int] NOT NULL,
    [server] [sysname] NOT NULL
) ON [PRIMARY]
GO

Finally, if you check the source code of sp_purge_jobhistory, you'll see it deletes data from msdb.dbo.sysjobhistory without removing any kind of referenced data from other places.


I tested the reseed in my Lab environment and when the seed was a number bigger than the last used value everything worked fine afterwards, but when I used a seed smaller than the last used value the job didn't register the execution history properly (but the job itself executed successfully). Therefore, make sure that max(id) from prod server + 1 is set properly, or you'll end up missing some history.

Conclusion: I think you're safe to reseed the identity.

2
  • Awesome! Thanks for the feedback. I'll be testing this theory in our beta environment shortly. The re-seed should always be much bigger, for example, current "prod" value is 83722833 and current max "passive" value is 44210436.
    – RickS
    Jun 15, 2021 at 18:22
  • Hello, @RickS. Were you able to test it?
    – Ronaldo
    Jul 7, 2021 at 2:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.