Søren Eggert Lundsteen Olsen
631edc2adc
Some checks failed
deploy community / deploy (push) Successful in 15s
deploy hub / deploy (push) Successful in 19s
CI / Build og unit tests (push) Successful in 7s
CI / MSI-installer (push) Successful in 17s
CI / Integration (mcr.microsoft.com/mssql/server:2017-latest) (push) Has been cancelled
CI / Integration (mcr.microsoft.com/mssql/server:2019-latest) (push) Has been cancelled
CI / Integration (mcr.microsoft.com/mssql/server:2022-latest) (push) Has been cancelled
CI / Integration (mcr.microsoft.com/mssql/server:2025-latest) (push) Has been cancelled
Registers 1.3's SQL in SqlScriptSources (DatabaseDiscovery: databases 2005/2000 and the Dynamics AX signature per database; PermissionCheck: login/sysadmin, the HAS_PERMS_BY_NAME checks for 2005-2012, 2014-2019/MI and 2022+, databases without access) and adds _collectors/DatabaseDiscovery.sql and PermissionCheck.sql with Danish review headers. The constants are renamed *Sql and the login query moved to PermissionCheck.WhoSql so the registry test finds them. The scripts of the collectors that now skip unchosen databases say so (their SQL is unchanged). Co-Authored-By: Claude Opus 5.5 (1M context) <noreply@anthropic.com>
192 lines
11 KiB
Transact-SQL
192 lines
11 KiB
Transact-SQL
-- ===================================================================================================================
|
||
-- Collector: DatabaseCollector ("databases")
|
||
-- ===================================================================================================================
|
||
-- Hvad: Databaseindstillinger og -tilstand, backup-historik (msdb), seneste fejlfri CHECKDB, VLF'er, Query
|
||
-- Store-tilstand og filer.
|
||
-- Fil: src/SQLInsight.Collectors/Databases/DatabaseCollector.cs
|
||
-- Databaser: Fra 1.3 kun de databaser, der er valgt under Servere og databaser (systemdatabaserne altid; se
|
||
-- _collectors/DatabaseDiscovery.sql). Fravalgte databaser sorteres fra i koden efter forespørgslen; SQL'en er uændret.
|
||
-- Interval: 15 min som standard (SQLInsight:CollectorIntervals:databases)
|
||
-- Versioner: Alle (SQL Server 2000 og nyere); CHECKDB og VLF'er fra 2016 SP2; Query Store fra 2016
|
||
--
|
||
-- 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];
|
||
--
|
||
-- Bruges af: SQLI-DB-001, SQLI-DB-002, SQLI-DB-003, SQLI-DB-004, SQLI-DB-005, SQLI-DB-006, SQLI-DB-007,
|
||
-- SQLI-DB-008, SQLI-DB-009, SQLI-DB-011, SQLI-DB-012, SQLI-DB-013, SQLI-DB-014, SQLI-DB-015,
|
||
-- SQLI-STAT-002
|
||
-- Read-only: Ja: kun SELECT samt SET-sessionsindstillinger. Intet ændrer data, indstillinger eller objekter.
|
||
-- Kørsel: Kør i SSMS som agentens login. Hver variant er sin egen batch (GO); en variant for en anden SQL Server-version
|
||
-- end din kan fejle - det er forventet. Agenten kører altid med sessionsindstillingerne i _collectors/InstanceProbe.sql.
|
||
-- ===================================================================================================================
|
||
|
||
-- 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
|