Invalid usage of the option flush_interval_seconds in the ALTER DATABASE statement

Question

I have created a database project in visual studio, but when I publish I get the error:

Invalid usage of the option flush_interval_seconds in the ALTER DATABASE statement

And the code that is generated is:

Msg 153, Level 15, State 5, Line 5
Invalid usage of the option flush_interval_seconds in the ALTER DATABASE statement.
(43,0): SQL72045: Script execution error. The executed script:

IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET QUERY_STORE (DATA_FLUSH_INTERVAL_SECONDS = 0, INTERVAL_LENGTH_MINUTES = 0) 
            WITH ROLLBACK IMMEDIATE;
    END

SQL72014: .Net SqlClient Data Provider:
Msg 153, Level 16, State 6, Line 5
Invalid usage of the option interval_length_minutes in the ALTER DATABASE statement.

(43,0): SQL72045: Script execution error. The executed script:

IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET QUERY_STORE (DATA_FLUSH_INTERVAL_SECONDS = 0, INTERVAL_LENGTH_MINUTES = 0) 
            WITH ROLLBACK IMMEDIATE;
    END

An error occurred while the batch was being executed.

I am struggling to find any reference on this, and what causes it, and ultimately how to fix it, so any guidance is appreciated.

Edit: the above SQL is auto generated when the DB is published from Visual Studio

That SQL, have you written that or is that generated by Visual Studio? — sticky bit
Sorry, I should have said, that is auto generated SQL when the DB is published from Visual Studio. — Keith

Lukasz Szozda Lukasz Szozda · Accepted Answer · 2019-01-27T18:08:25

You could set it up using Visual Studio.

Solution Explorer-> Database Project -> Right Click -> Properties -> Project Settings -> Database Settings -> Query Store

Change 0 to desired value.

Related: SSDT: SQL Project Options and Database Project Settings

If this does not work, it means that you have custom SQL Script in Pre/Post Deployment folder.

Invalid usage of the option flush_interval_seconds in the ALTER DATABASE statement

2 Answers