I want to start using Query Store with suggested setting by Erin Stellato.
USE [master]
GO
ALTER DATABASE DatabaseName SET QUERY_STORE = ON
GO
ALTER DATABASE DatabaseName SET QUERY_STORE (OPERATION_MODE = READ_WRITE
, INTERVAL_LENGTH_MINUTES = 30
, MAX_STORAGE_SIZE_MB = 1000
, QUERY_CAPTURE_MODE = AUTO)
GO
But that is a by database setting, and I want to be able to activate on all the databases of an instance at once.
I found SET RECOVERY Model Using sp_msforeachdb (at serverfault)
I can use the OP's solution
EXECUTE master.sys.sp_MSforeachdb 'USE [?];
IF DB_ID() >= 5
ALTER DATABASE [?] SET QUERY_STORE = ON
ALTER DATABASE [?] SET QUERY_STORE (OPERATION_MODE = READ_WRITE
, INTERVAL_LENGTH_MINUTES = 30
, MAX_STORAGE_SIZE_MB = 1000
, QUERY_CAPTURE_MODE = AUTO)
'
While it works I get the errors
Msg 12438, Level 16, State 1, Line 41
Cannot perform action because Query Store cannot be enabled on system database master.
Msg 5069, Level 16, State 1, Line 41
ALTER DATABASE statement failed.
Msg 12438, Level 16, State 1, Line 41
Cannot perform action because Query Store cannot be enabled on system database tempdb.
Msg 5069, Level 16, State 1, Line 41
ALTER DATABASE statement failed.
I tried several variations of the solution by Jason Cumberland but could not get it working.
set quoted_identifier on
EXECUTE master.sys.sp_MSforeachdb '
IF '?' not in ('tempdb','master','model')
begin
exec (ALTER DATABASE [?] SET QUERY_STORE = ON
ALTER DATABASE [?] SET QUERY_STORE (OPERATION_MODE = READ_WRITE
, INTERVAL_LENGTH_MINUTES = 30
, MAX_STORAGE_SIZE_MB = 1000
, QUERY_CAPTURE_MODE = AUTO))
end
'
I am getting the error
Msg 102, Level 15, State 1, Line 25
Incorrect syntax near '?'.
These two work
--Turn off on all databases
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; ALTER DATABASE [?] SET QUERY_STORE = OFF'
and
--Check all Query Store database settings at once
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; SELECT * FROM sys.database_query_store_options'
Question: How can I turn on Query Store on all the database of an instance without getting an error message?
Note: of the system databases msdb is the only one that that allows query store. I am not excluding it currently.
I am using SSMS 17.4 against, SQL instance running SQL 2016 (SP1)