Yes. (See the REPLICATION section in Editions and supported features of SQL Server 2016
Yes. The distributor (Distribution Server) is located on a SQL Server instance and has to be correctly licensed, be this a production server or development server.
Yes. License required. A replication from a production server to another (dev) server is considered a move of productive data and so the target is no longer a DEV server, but a productive server.
The basic premise regarding Microsoft Licenses for SQL Server is: If it contains productive data, it is a productive server and requires (correct) licensing.
IMHO : Do no try to circumvent MS licensing fees by being clever and innovative. It will almost always backfire.
DISCLAIMER: Please contact a Microsoft SQL Server licensee/specialist/re-seller or Microsoft themselves to obtain information about correct licensing. No guarantee given for above made statements.
In both cases for the attempted queries in the question, the problems are simply syntactical.
In the first case: You have an IF
condition, but do not group the statements after it together, so only the statement immediately following the IF
is conditional. The second ALTER DATABASE
statement always executes. To fix, do this:
EXECUTE master.sys.sp_MSforeachdb N'USE [?];
IF DB_ID() >= 5
BEGIN
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;
';
You probably also no not need the USE
statement. It could be removed and then the DB_ID()
could be changed into DB_ID(N''?'')
.
In the second case: You did not escape the embedded single-quotes. You also had an extraneous EXEC
, and you did not include the msdb
system database. To fix all of that, do the following:
EXECUTE master.sys.sp_MSforeachdb N'
IF (N''?'' NOT IN (N''tempdb'', N''master'', N''model'', N''msdb''))
BEGIN
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;
';
PLEASE NOTE: @Kris is not incorrect in saying that "sp_MSForeachDB
is undocumented and unsupported", and the advice to use another mechanism to cycle through DBs is good advice. It might not be worth the trouble if this is a one-time task, but if this code is to be used repeatedly, then yes, you should probably heed that warning.
Best Answer
As sp_BlitzErik mentioned in a comment, and as I gathered from my own research, you can find some things in
sys.query_store_query_text
but not necessarily always aSHRINK
.This query returns 22,497 results:
This query does not return any, except the second time I run it :)
The foundation of the query above comes from a SQL with Bert post.
A coworker has shown me a query that finds the shrink and the time and login of who did it. So I have what I need for the my specific issue, but that is something of a side step from my question.
My workaround uses the default query trace. It shows multiple recent
DBCC Shrinkdatabase ('MyDB', TRUNCATEONLY)
that are run against the master database, which does not have query store.A
DBCC SHRINKFILE
run in the context of a database with query store enabled does get recorded in the query store.