In the documentation for creating a job database that is then used by an Elastic Job Agent, there is this emphasis on a JobDatabase. It is written that:
The Job database doesn't literally need to be new, but should be a clean, empty, S0 or higher service objective.
Having spent days to create or test jobs, resource groups, credentials to no avail on my current database, I wonder if I should create an extra database dedicated for jobs. But I don't understand why this database should be clean and empty.
The Job database doesn't literally need to be new, but should be a clean, empty, S0 or higher service objective.
Why this database should be clean and empty?
Because an Elastic Job agent is the Azure resource for creating, running, and managing jobs. It needs metadata, logs, results, job definitions, and also contains many useful stored procedures and other database objects for creating, running, and managing jobs using T-SQL. When we create an Elastic Job agent on a exited database(instead of independently created a Job Database), it will create the meatadata(such as tables, store procedures and so on) on the exited database. So there may be schema conflicts(or another elastic job agent has been created before). I think this is the main reason.