Søren Eggert Lundsteen Olsen
2284e7f082
A DBA should be able to review every statement SQLInsight sends to a monitored server before granting it access. "SQL Scripts/<rule-id>.sql" holds, per rule (82 built-in, 16 community), a Danish header (what and why, how C# judges the data, thresholds with config keys, severity, exact GRANTs, versions, collector file and member) and the collector's SQL verbatim, every version variant as its own batch. "_collectors/<Collector>.sql" holds each collector's queries once. The only statement that is not strictly read-only is documented in the scripts: the deadlock collector's session temp table (#system_health). Inline SQL in the collectors moved to internal constants and Build*Sql methods (no text changed), so SqlScriptTests can check that the scripts still contain the SQL the code sends, that every rule has a script, that every collector query is registered, and that nothing writes. Co-Authored-By: Claude Opus 5.5 (1M context) <noreply@anthropic.com>
223 lines
12 KiB
Transact-SQL
223 lines
12 KiB
Transact-SQL
-- ===================================================================================================================
|
||
-- SQLI-DB-003: AUTO_CLOSE er slået til
|
||
-- ===================================================================================================================
|
||
-- Regel-id: SQLI-DB-003
|
||
-- Titel: AUTO_CLOSE er slået til
|
||
-- Alvor: Medium
|
||
-- Versioner: Alle (SQL Server 2000 og nyere).
|
||
--
|
||
-- Hvad tjekkes, og hvorfor:
|
||
-- Om AUTO_CLOSE er slået til, så databasen lukkes og plan cache tømmes, når sidste forbindelse slipper den.
|
||
--
|
||
-- Vurdering (sker i C# ud fra de indsamlede data, ikke i SQL'en, medmindre andet er nævnt):
|
||
-- Ét fund pr. ONLINE database med is_auto_close_on = 1.
|
||
--
|
||
-- Grænseværdier (konfigurationsnøgle i agentens appsettings.json og standardværdi):
|
||
-- Ingen.
|
||
--
|
||
-- Rettigheder til agentens login (se db/permissions/grant-least-privilege.sql; [agent] = agentens login):
|
||
-- GRANT VIEW SERVER STATE TO [agent]; -- SQL Server 2005-2019
|
||
-- GRANT VIEW SERVER PERFORMANCE STATE TO [agent]; -- SQL Server 2022+ (erstatter VIEW SERVER STATE)
|
||
-- GRANT VIEW SERVER SECURITY STATE TO [agent]; -- SQL Server 2022+
|
||
-- GRANT VIEW ANY DEFINITION TO [agent]; -- metadata; giver IKKE adgang til data
|
||
-- GRANT CONNECT ANY DATABASE TO [agent]; -- SQL Server 2014+; giver IKKE adgang til data
|
||
-- Før 2014 i stedet, i hver brugerdatabase: CREATE USER [agent] FOR LOGIN [agent];
|
||
-- USE msdb; GRANT SELECT ON dbo.backupset TO [agent];
|
||
--
|
||
-- Data kommer fra (collector, fil : medlemmer, interval):
|
||
-- DatabaseCollector
|
||
-- Fil: src/SQLInsight.Collectors/Databases/DatabaseCollector.cs
|
||
-- Medlemmer: DatabaseCollector.BuildSql2005(instance, capabilities), DatabaseCollector.BuildSql2000(),
|
||
-- DatabaseCollector.Files2005, DatabaseCollector.Files2000, Db.UtcOffsetSql, DatabaseCollector.VlfSql,
|
||
-- DatabaseCollector.BuildQueryStoreSql(database)
|
||
-- Interval: 15 min som standard (SQLInsight:CollectorIntervals:databases)
|
||
-- Delt script: SQL Scripts/_collectors/DatabaseCollector.sql
|
||
-- Regelkode: src/SQLInsight.Rules/Databases/DatabaseRules.cs : SimpleDatabaseRules.Create (DatabaseSettingRule
|
||
-- "SQLI-DB-003")
|
||
--
|
||
-- Read-only: Ja: kun SELECT samt SET-sessionsindstillinger. Intet ændrer data, indstillinger eller objekter.
|
||
-- Kørsel: Kør i SSMS som agentens login (eller et login med samme rettigheder). Hver variant er sin egen batch
|
||
-- (GO); en variant for en anden SQL Server-version end din kan fejle - det er forventet.
|
||
-- Per-database-forespørgsler er vist for databasen msdb, som findes overalt: ret navnet til den
|
||
-- database, du vil se.
|
||
-- ===================================================================================================================
|
||
|
||
-- -------------------------------------------------------------------------------------------------------------------
|
||
-- Forbindelsen: Sessionsindstillinger, agenten sætter på hver forbindelse
|
||
-- -------------------------------------------------------------------------------------------------------------------
|
||
|
||
-- Variant: Alle versioner: sættes på hver forbindelse, agenten åbner
|
||
-- Kilde: SqlConnectionFactory.SessionSettings
|
||
-- Kode: src/SQLInsight.Connectivity/SqlConnectionFactory.cs : SqlConnectionFactory.SessionSettings
|
||
SET LOCK_TIMEOUT 5000; SET DEADLOCK_PRIORITY LOW; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
|
||
GO
|
||
|
||
-- -------------------------------------------------------------------------------------------------------------------
|
||
-- DatabaseCollector: Databaseindstillinger og -tilstand, backup-historik (msdb), seneste fejlfri CHECKDB, VLF'er,
|
||
-- Query Store-tilstand og filer.
|
||
-- -------------------------------------------------------------------------------------------------------------------
|
||
|
||
-- Variant: SQL Server 2016 SP2 og nyere (med LastGoodCheckDbTime og availability groups)
|
||
-- Kilde: DatabaseCollector.BuildSql2005(2016 SP2+)
|
||
-- Kode: src/SQLInsight.Collectors/Databases/DatabaseCollector.cs : DatabaseCollector.BuildSql2005(instance, capabilities)
|
||
SELECT d.name, d.database_id, d.state_desc, d.recovery_model_desc, d.compatibility_level, d.page_verify_option_desc,
|
||
d.is_auto_close_on, d.is_auto_shrink_on, d.is_read_only, d.is_trustworthy_on, d.create_date,
|
||
CASE WHEN EXISTS (SELECT 1 FROM sys.database_mirroring m WHERE m.database_id = d.database_id AND m.mirroring_role = 2)
|
||
OR EXISTS (SELECT 1 FROM sys.dm_hadr_database_replica_states rs
|
||
JOIN sys.dm_hadr_availability_replica_states ars ON ars.replica_id = rs.replica_id
|
||
WHERE rs.database_id = d.database_id AND rs.is_local = 1 AND ars.role = 2)
|
||
THEN 1 ELSE 0 END,
|
||
CASE WHEN d.replica_id IS NOT NULL THEN 1 ELSE 0 END,
|
||
CASE WHEN b.last_full >= DATEADD(second, -1, d.create_date) THEN b.last_full END,
|
||
CASE WHEN b.last_diff >= DATEADD(second, -1, d.create_date) THEN b.last_diff END,
|
||
CASE WHEN b.last_log >= DATEADD(second, -1, d.create_date) THEN b.last_log END,
|
||
CAST(DATABASEPROPERTYEX(d.name, 'LastGoodCheckDbTime') AS datetime),
|
||
d.is_auto_create_stats_on, d.is_auto_update_stats_on
|
||
FROM sys.databases d
|
||
LEFT JOIN (
|
||
SELECT database_name,
|
||
MAX(CASE WHEN type = 'D' THEN backup_finish_date END) AS last_full,
|
||
MAX(CASE WHEN type = 'I' THEN backup_finish_date END) AS last_diff,
|
||
MAX(CASE WHEN type = 'L' THEN backup_finish_date END) AS last_log,
|
||
MIN(backup_finish_date) AS first_backup
|
||
FROM msdb.dbo.backupset
|
||
GROUP BY database_name
|
||
) b ON b.database_name = d.name
|
||
WHERE d.name <> N'tempdb'
|
||
GO
|
||
|
||
-- Variant: SQL Server 2012 – 2016 SP1
|
||
-- Kilde: DatabaseCollector.BuildSql2005(2012)
|
||
-- Kode: src/SQLInsight.Collectors/Databases/DatabaseCollector.cs : DatabaseCollector.BuildSql2005(instance, capabilities)
|
||
SELECT d.name, d.database_id, d.state_desc, d.recovery_model_desc, d.compatibility_level, d.page_verify_option_desc,
|
||
d.is_auto_close_on, d.is_auto_shrink_on, d.is_read_only, d.is_trustworthy_on, d.create_date,
|
||
CASE WHEN EXISTS (SELECT 1 FROM sys.database_mirroring m WHERE m.database_id = d.database_id AND m.mirroring_role = 2)
|
||
OR EXISTS (SELECT 1 FROM sys.dm_hadr_database_replica_states rs
|
||
JOIN sys.dm_hadr_availability_replica_states ars ON ars.replica_id = rs.replica_id
|
||
WHERE rs.database_id = d.database_id AND rs.is_local = 1 AND ars.role = 2)
|
||
THEN 1 ELSE 0 END,
|
||
CASE WHEN d.replica_id IS NOT NULL THEN 1 ELSE 0 END,
|
||
CASE WHEN b.last_full >= DATEADD(second, -1, d.create_date) THEN b.last_full END,
|
||
CASE WHEN b.last_diff >= DATEADD(second, -1, d.create_date) THEN b.last_diff END,
|
||
CASE WHEN b.last_log >= DATEADD(second, -1, d.create_date) THEN b.last_log END,
|
||
CAST(NULL AS datetime),
|
||
d.is_auto_create_stats_on, d.is_auto_update_stats_on
|
||
FROM sys.databases d
|
||
LEFT JOIN (
|
||
SELECT database_name,
|
||
MAX(CASE WHEN type = 'D' THEN backup_finish_date END) AS last_full,
|
||
MAX(CASE WHEN type = 'I' THEN backup_finish_date END) AS last_diff,
|
||
MAX(CASE WHEN type = 'L' THEN backup_finish_date END) AS last_log,
|
||
MIN(backup_finish_date) AS first_backup
|
||
FROM msdb.dbo.backupset
|
||
GROUP BY database_name
|
||
) b ON b.database_name = d.name
|
||
WHERE d.name <> N'tempdb'
|
||
GO
|
||
|
||
-- Variant: SQL Server 2005 – 2008 R2
|
||
-- Kilde: DatabaseCollector.BuildSql2005(2005)
|
||
-- Kode: src/SQLInsight.Collectors/Databases/DatabaseCollector.cs : DatabaseCollector.BuildSql2005(instance, capabilities)
|
||
SELECT d.name, d.database_id, d.state_desc, d.recovery_model_desc, d.compatibility_level, d.page_verify_option_desc,
|
||
d.is_auto_close_on, d.is_auto_shrink_on, d.is_read_only, d.is_trustworthy_on, d.create_date,
|
||
CASE WHEN EXISTS (SELECT 1 FROM sys.database_mirroring m WHERE m.database_id = d.database_id AND m.mirroring_role = 2)
|
||
|
||
THEN 1 ELSE 0 END,
|
||
0,
|
||
CASE WHEN b.last_full >= DATEADD(second, -1, d.create_date) THEN b.last_full END,
|
||
CASE WHEN b.last_diff >= DATEADD(second, -1, d.create_date) THEN b.last_diff END,
|
||
CASE WHEN b.last_log >= DATEADD(second, -1, d.create_date) THEN b.last_log END,
|
||
CAST(NULL AS datetime),
|
||
d.is_auto_create_stats_on, d.is_auto_update_stats_on
|
||
FROM sys.databases d
|
||
LEFT JOIN (
|
||
SELECT database_name,
|
||
MAX(CASE WHEN type = 'D' THEN backup_finish_date END) AS last_full,
|
||
MAX(CASE WHEN type = 'I' THEN backup_finish_date END) AS last_diff,
|
||
MAX(CASE WHEN type = 'L' THEN backup_finish_date END) AS last_log,
|
||
MIN(backup_finish_date) AS first_backup
|
||
FROM msdb.dbo.backupset
|
||
GROUP BY database_name
|
||
) b ON b.database_name = d.name
|
||
WHERE d.name <> N'tempdb'
|
||
GO
|
||
|
||
-- Variant: SQL Server 2000 (legacy ODBC-sti)
|
||
-- Kilde: DatabaseCollector.BuildSql2000
|
||
-- Kode: src/SQLInsight.Collectors/Databases/DatabaseCollector.cs : DatabaseCollector.BuildSql2000()
|
||
SELECT d.name, d.dbid,
|
||
CAST(DATABASEPROPERTYEX(d.name, 'Status') AS nvarchar(60)),
|
||
CAST(DATABASEPROPERTYEX(d.name, 'Recovery') AS nvarchar(60)),
|
||
d.cmptlevel,
|
||
CASE WHEN DATABASEPROPERTYEX(d.name, 'IsTornPageDetectionEnabled') = 1 THEN N'TORN_PAGE_DETECTION' ELSE N'NONE' END,
|
||
CAST(DATABASEPROPERTYEX(d.name, 'IsAutoClose') AS int),
|
||
CAST(DATABASEPROPERTYEX(d.name, 'IsAutoShrink') AS int),
|
||
CASE WHEN DATABASEPROPERTYEX(d.name, 'Updateability') = 'READ_ONLY' THEN 1 ELSE 0 END,
|
||
0,
|
||
d.crdate,
|
||
0,
|
||
0,
|
||
CASE WHEN b.last_full >= DATEADD(second, -1, d.crdate) THEN b.last_full END,
|
||
CASE WHEN b.last_diff >= DATEADD(second, -1, d.crdate) THEN b.last_diff END,
|
||
CASE WHEN b.last_log >= DATEADD(second, -1, d.crdate) THEN b.last_log END,
|
||
CAST(NULL AS datetime),
|
||
CAST(DATABASEPROPERTYEX(d.name, 'IsAutoCreateStatistics') AS int),
|
||
CAST(DATABASEPROPERTYEX(d.name, 'IsAutoUpdateStatistics') AS int)
|
||
FROM master.dbo.sysdatabases d
|
||
LEFT JOIN (
|
||
SELECT database_name,
|
||
MAX(CASE WHEN type = 'D' THEN backup_finish_date END) AS last_full,
|
||
MAX(CASE WHEN type = 'I' THEN backup_finish_date END) AS last_diff,
|
||
MAX(CASE WHEN type = 'L' THEN backup_finish_date END) AS last_log,
|
||
MIN(backup_finish_date) AS first_backup
|
||
FROM msdb.dbo.backupset
|
||
GROUP BY database_name
|
||
) b ON b.database_name = d.name
|
||
WHERE d.name <> N'tempdb'
|
||
GO
|
||
|
||
-- Variant: SQL Server 2005 og nyere
|
||
-- Kilde: DatabaseCollector.Files2005
|
||
-- Kode: src/SQLInsight.Collectors/Databases/DatabaseCollector.cs : DatabaseCollector.Files2005
|
||
SELECT database_id, name, type_desc, physical_name,
|
||
CAST(size AS bigint) / 128,
|
||
CASE WHEN is_percent_growth = 1 THEN growth ELSE CAST(growth AS bigint) / 128 END,
|
||
is_percent_growth,
|
||
CASE WHEN max_size = -1 THEN -1 ELSE CAST(max_size AS bigint) / 128 END
|
||
FROM sys.master_files
|
||
WHERE type_desc IN (N'ROWS', N'LOG')
|
||
GO
|
||
|
||
-- Variant: SQL Server 2000 (legacy ODBC-sti)
|
||
-- Kilde: DatabaseCollector.Files2000
|
||
-- Kode: src/SQLInsight.Collectors/Databases/DatabaseCollector.cs : DatabaseCollector.Files2000
|
||
SELECT dbid, name, CASE WHEN groupid = 0 THEN N'LOG' ELSE N'ROWS' END, filename,
|
||
CAST(size AS bigint) / 128,
|
||
CASE WHEN status & 0x100000 <> 0 THEN growth ELSE CAST(growth AS bigint) / 128 END,
|
||
CASE WHEN status & 0x100000 <> 0 THEN 1 ELSE 0 END,
|
||
CASE WHEN maxsize = -1 THEN -1 ELSE CAST(maxsize AS bigint) / 128 END
|
||
FROM master.dbo.sysaltfiles
|
||
GO
|
||
|
||
-- Variant: Alle versioner: serverens afstand til UTC (msdb-tider er lokal tid)
|
||
-- Kilde: Db.UtcOffsetSql
|
||
-- Kode: src/SQLInsight.Collectors/Db.cs : Db.UtcOffsetSql
|
||
SELECT DATEDIFF(minute, GETUTCDATE(), GETDATE())
|
||
GO
|
||
|
||
-- Variant: SQL Server 2016 SP2 og nyere (sys.dm_db_log_info)
|
||
-- Kilde: DatabaseCollector.VlfSql
|
||
-- Kode: src/SQLInsight.Collectors/Databases/DatabaseCollector.cs : DatabaseCollector.VlfSql
|
||
SELECT d.database_id, COUNT(*)
|
||
FROM sys.databases d
|
||
CROSS APPLY sys.dm_db_log_info(d.database_id) li
|
||
WHERE d.state = 0
|
||
GROUP BY d.database_id
|
||
GO
|
||
|
||
-- Variant: SQL Server 2016 og nyere: én gang pr. online brugerdatabase (vist for msdb)
|
||
-- Kilde: DatabaseCollector.BuildQueryStoreSql
|
||
-- Kode: src/SQLInsight.Collectors/Databases/DatabaseCollector.cs : DatabaseCollector.BuildQueryStoreSql(database)
|
||
SELECT desired_state_desc, actual_state_desc, readonly_reason, current_storage_size_mb, max_storage_size_mb
|
||
FROM [msdb].sys.database_query_store_options
|
||
GO
|