Search code examples
azure-sql-databasesql-query-store

max query store size in sql database getting reverted

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?

Solution

    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: enter image description here

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

    5. Save Project and commit .sqlproj file.

    6. You would see new line like below added to your .sqlproj: enter image description here