0

We are running Azure SQL Managed Instance 2019 and need to change the max job history settings in SQL Server agent.

On a non-Azure-hosted SQL Server, it can be done by going to SQL Server Agent Properties, the History tab.

enter image description here

But, this tab is not available in Azure SQL Managed Instance.

Is there a different way (via SQL or Azure portal or some other way) to change the max history settings on Azure SQL Managed Instance?

user1044169
  • 2,686
  • 6
  • 35
  • 64
  • On your desktop, change the setting, then hit the Script button. It will produce the T-SQL that this pointy-clicky dialog _would have_ issued if you pressed OK. Now tweak it and try to run that on MI. It *might* be that you don't have the ability to change the setting (I haven't tried it), but at least you'll get a useful error message instead of "the pointy-clicky dialog isn't there." – Aaron Bertrand Feb 03 '22 at 17:43
  • That was a good suggestion, but I got "Msg 41905, Level 16, State 1, Procedure msdb.dbo.sp_set_sqlagent_properties, Line 44 [Batch Start Line 19] Stored procedure sp_set_sqlagent_properties is not supported in SQL Database Managed Instance." – user1044169 Feb 04 '22 at 02:15

1 Answers1

0

Initially I thought maybe this was just SSMS conditionally not showing that page because of a specific incompatibility:

On your desktop, change the setting, then hit the Script button. It will produce the T-SQL that this pointy-clicky dialog would have issued if you pressed OK. Now tweak it and try to run that on MI. It might be that you don't have the ability to change the setting (I haven't tried it), but at least you'll get a useful error message instead of "the pointy-clicky dialog isn't there."

But it seems this is by design, and changing these properties is not possible on the current version of Managed Instance (because they are, for some reason, still stored in the registry). From T-SQL differences between SQL Server & Azure SQL Managed Instance:

  • SQL Server Agent settings are read only. The procedure sp_set_agent_properties isn't supported in SQL Managed Instance.

And from Automate management tasks using SQL Agent jobs in Azure SQL Managed Instance:

SQL Managed Instance currently doesn't allow you to change any SQL Agent properties because they are stored in the underlying registry values. This means options for adjusting the Agent retention policy for job history records are fixed at the default of 1000 total records and max 100 history records per job.

You can send feedback through your account rep to express a business need for this functionality, but I wonder if you can manage this retention yourself in some way:

  • if you want to store less history, you should be able to manually delete rows from msdb.dbo.sysjobhistory to purge data more aggressively than the defaults (also, looks like manually calling sp_purge_jobhistory is supported in MI).
  • if you want to store more history, you can archive rows from sysjobhistory into your own tables that you can use to review, however these rows won't get pulled into the Agent UIs. I suppose you could insert older data back into sysjobhistory frequently enough that it is available to UIs (until it gets deleted again), but that could get messy and hard to manage.

I have written about managing Agent history more effectively than the default behaviors and limits allow, but it probably isn't going to solve your entire issue:

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490