Query store - bumping up max_storage_size_mb (Query Store Retention, Max Size) works for a short while then goes back to 250?

I've been unsuccessful doing this a few times now. In the UI I'm able to successfully bump up the size of the query store in Query Store Retention setting from 250 to 1000 mb. I make the change, close the database properties window, reopen, and it looks like the change was successful, shows 1000 mb.
Then when I look at it again (maybe the next day or so) it's reverted back to 250 mb.

The version is Microsoft SQL Server 2017 (RTM-CU13) (KB4466404) - 14.0.3048.4.

What could be happening here?

The setting shouldn't change back on its own.

You can see who or what is changing it, and when, by setting up an Extended Events session like this one:

CREATE EVENT SESSION [alter_statements] ON SERVER 
ADD EVENT sqlserver.object_altered
(
    ACTION(sqlserver.nt_username,sqlserver.server_principal_name)
    WHERE ([object_name]=N'QDSTest')
)
ADD TARGET package0.event_file (SET filename=N'alter_statements')
WITH (STARTUP_STATE=OFF)
GO

ALTER EVENT SESSION [alter_statements] ON SERVER 
STATE = START;
GO

You would need to replace "QDSTest" with the database name of interest in your case.

With that you should be able to find the username associated with the session that issued the ALTER TABLE statement that changed your query store settings, which will hopefully be helpful in tracking down who or what is changing that.

One possibility is that you have automated deployments to this database that use a model-based approach (like SQL Server Data Tools dacpac deployments). If the "model" has query store configured a certain way, each deployment will try to change the settings back to what is in the model.

StackExchange Database Administrators Q#297794, answer score: 6