max query store size in sql database getting reverted

Question

I used below query to increase max query store size:

> ALTER DATABASE [QueryStoreDB] SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 1024);

But after SQL DevOps deployment, it was getting reverted to default 100 MB.

How to keep it always at 1000 MB and stop getting reverted?

Answer 1

1. You need to open SQL project in Visual Studio.

2. Right-click on project and select 'properties'.

3. Click on database settings as shown below:

4. In 'operational', you need to tweak the 'Max Storage Size (MB)' value from 100 to 1000.

5. Save Project and commit .sqlproj file.

6. You would see new line like below added to your .sqlproj: