Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- WITH DBsize AS
- (
- SELECT D1.[name] AS DatabaseName, SUM(CAST(F1.size as bigint)) * 8192 / 1024 / 1024 AS DatabaseSizeMB
- FROM sys.databases AS D1
- JOIN sys.master_files AS F1
- ON D1.database_id = F1.database_id
- WHERE D1.database_id > 4 AND F1.type_desc = 'ROWS'
- GROUP BY D1.[name]
- )
- , SizeRules AS
- (
- SELECT DatabaseName, DatabaseSizeMB
- , CASE
- WHEN CAST(DatabaseSizeMB * 0.1 AS bigint) < 1024
- THEN 1024
- ELSE CAST(DatabaseSizeMB * 0.1 AS bigint)
- END QuerystoreMaxSize
- , 15 AS QueryStaleDays
- FROM DBSize
- )
- SELECT DatabaseName, DatabaseSizeMB, QuerystoreMaxSize, QueryStaleDays
- , 'ALTER DATABASE [' + DatabaseName + '] SET QUERY_STORE = ON; '
- + 'ALTER DATABASE [' + DatabaseName + '] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, '
- + 'CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = ' + CAST(QueryStaleDays AS varchar) +'), '
- + 'MAX_STORAGE_SIZE_MB = ' + CAST(QuerystoreMaxSize as varchar) + ', QUERY_CAPTURE_MODE = AUTO);'
- FROM SizeRules
- ORDER BY DatabaseName
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement