Søren Eggert Lundsteen Olsen
be039a6f73
AxSql.Reset ran SET LOCK_TIMEOUT -1 and READ COMMITTED after every AX read (and after the AX detection in discovery). The collectors of an instance share one connection, so every collector after the AX ones waited on locks without limit instead of the agent's 5 s, and no longer read uncommitted. Reset now restores the agent's own settings, from one place (Core.Instances.AgentSession) that SqlConnectionFactory.SessionSettings also uses. The AX reads themselves are unchanged: READ COMMITTED (or SNAPSHOT) with a 2 s lock timeout inside sp_executesql, never NOLOCK. Co-Authored-By: Claude Opus 5.5 (1M context) <noreply@anthropic.com>
130 lines
11 KiB
MySQL
130 lines
11 KiB
MySQL
-- ===================================================================================================================
|
|
-- SQLI-AX-009: Dynamics AX RecId er ved at løbe tør
|
|
-- ===================================================================================================================
|
|
-- Regel-id: SQLI-AX-009
|
|
-- Titel: Dynamics AX RecId er ved at løbe tør
|
|
-- Alvor: Axapta 3.0 (32-bit): High fra advarselsgrænsen, Critical fra den kritiske grænse; 64-bit: High; altid Critical, når RecId er opbrugt.
|
|
-- Versioner: SQL Server 2005 og nyere (ikke 2000). Axapta 3.0: verificeret mod en kundes skema (docs/dynamics-ax/
|
|
-- schema-ax30.tsv). AX 4.0, AX 2009, AX 2012 og F&O: baseret på Microsofts dokumenterede skema - ikke
|
|
-- verificeret mod kundedata.
|
|
--
|
|
-- Hvad tjekkes, og hvorfor:
|
|
-- Når en tabels RecId-serie er brugt op, kan AX ikke oprette flere poster. Axapta 3.0 har 32-bit RecId (int, højst
|
|
-- 2.147.483.647 pr. tabel og firma), og det løses kun med et stort projekt - derfor advares langt tidligere for 3.0.
|
|
--
|
|
-- Vurdering (sker i C# ud fra de indsamlede data, ikke i SQL'en, medmindre andet er nævnt):
|
|
-- Grænse = typens maksimum (int, når SYSTEMSEQUENCES.NEXTVAL er int som i Axapta 3.0; ellers bigint), eller MAXVAL,
|
|
-- hvis den er sat lavere. Brugt % = NEXTVAL / grænse. Prognose ud fra SQLInsights egne daglige målinger af NEXTVAL
|
|
-- (insight.AxSequenceSamples). Højst 25 fund pr. database, de mest brugte først. Tal: src/SQLInsight.Core/DynamicsAx/AxSchemaAdapter.cs : Interpret(AxRecIdRow).
|
|
--
|
|
-- Grænseværdier (konfigurationsnøgle i agentens appsettings.json og standardværdi):
|
|
-- SQLInsight:DynamicsAx:RecId32WarnPercent = 50: Axapta 3.0: High fra så mange procent af int-intervallet
|
|
-- SQLInsight:DynamicsAx:RecId32CriticalPercent = 75: Axapta 3.0: Critical
|
|
-- SQLInsight:DynamicsAx:RecId32ForecastDays = 730: Axapta 3.0: High, hvis den løber tør inden for så mange dage;
|
|
-- Critical inden for halvdelen
|
|
-- SQLInsight:DynamicsAx:RecId64WarnPercent = 80: 64-bit (eller sænket MAXVAL): High
|
|
-- SQLInsight:DynamicsAx:ForecastWarnDays = 90: 64-bit: High, hvis den løber tør inden for så mange dage
|
|
-- SQLInsight:DynamicsAx:ForecastBasisDays = 30: prognosen bruger den ældste daglige måling inden for så mange dage
|
|
--
|
|
-- Rettigheder til agentens login (se db/permissions/grant-least-privilege.sql og grant-dynamics-ax-read.sql):
|
|
-- Valgfri AX-læseadgang, kun SELECT (db/permissions/grant-dynamics-ax-read.sql; [bruger] = agentens databasebruger):
|
|
-- Axapta 3.0: GRANT SELECT ON [bmssa].[SYSTEMSEQUENCES] TO [bruger];
|
|
-- GRANT SELECT ON [bmssa].[SQLDICTIONARY] TO [bruger]; -- valgfri: tabelnavne
|
|
-- AX 4.0/2009/2012/F&O: de samme tabeller i [dbo]
|
|
-- Mangler SYSTEMSEQUENCES, er reglen "ikke målbar" med den nøjagtige GRANT for databasen. Uden GRANT ændres intet.
|
|
-- Genkendelsen: kun grant-least-privilege.sql (VIEW ANY DEFINITION; CONNECT ANY DATABASE fra 2014, ellers en
|
|
-- databasebruger: CREATE USER [agent] FOR LOGIN [agent];).
|
|
--
|
|
-- Data kommer fra (collector, fil : medlemmer, interval):
|
|
-- AxDatabaseDetector (den fælles AX-genkendelse)
|
|
-- Fil: src/SQLInsight.Collectors/DynamicsAx/AxDatabaseDetector.cs
|
|
-- Medlemmer: AxDatabaseDetector.DatabasesSql, AxDatabaseDetector.BuildDetectionSql(database)
|
|
-- Interval: hver time pr. database (hvert 5. minut, mens AX-læseadgangen mangler), delt af alle AX-collectors
|
|
-- Delt script: SQL Scripts/_collectors/AxDatabaseDetector.sql
|
|
-- AxSequenceCollector
|
|
-- Fil: src/SQLInsight.Collectors/DynamicsAx/AxSequenceCollector.cs; SQL: src/SQLInsight.Core/DynamicsAx/AxSchemaAdapter.cs
|
|
-- Medlemmer: AxSchemaAdapter.NumberSequences(database), AxSchemaAdapter.RecIds(database), AxSql.InDatabase
|
|
-- Interval: 1 time som standard (SQLInsight:CollectorIntervals:dynamics-ax-sequences)
|
|
-- Delt script: SQL Scripts/_collectors/AxSequenceCollector.sql
|
|
-- Regelkode: src/SQLInsight.Rules/DynamicsAx/DynamicsAxRules.cs : AxRecIdExhaustionRule
|
|
--
|
|
-- Read-only: Ja: kun SELECT med TOP og SET-sessionsindstillinger. AX-tabellerne læses i AX-databasen via
|
|
-- [db].sys.sp_executesql (sessionen skifter aldrig database) under READ COMMITTED med SET LOCK_TIMEOUT 2000
|
|
-- (SNAPSHOT, når databasen tillader det), aldrig NOLOCK; bagefter sættes agentens sessionsindstillinger
|
|
-- igen. Intet ændrer data, indstillinger eller objekter. Kun i databaser genkendt som AX og valgt til
|
|
-- overvågning.
|
|
-- Kørsel: Kør i SSMS som agentens login (eller et login med samme rettigheder). Hver variant er sin egen batch
|
|
-- (GO); varianter for andre AX-versioner end din fejler eller giver ingen rækker - det er forventet.
|
|
-- AX-forespørgslerne er vist for databasen AXDB: ret [AXDB] til din AX-database.
|
|
-- ===================================================================================================================
|
|
|
|
-- -------------------------------------------------------------------------------------------------------------------
|
|
-- 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
|
|
|
|
-- -------------------------------------------------------------------------------------------------------------------
|
|
-- AxDatabaseDetector: Hvilke overvågede databaser er Dynamics AX (kun metadata)
|
|
-- -------------------------------------------------------------------------------------------------------------------
|
|
|
|
-- Variant: Alle versioner fra SQL Server 2005: online brugerdatabaser og deres snapshot-indstillinger
|
|
-- Kilde: AxDatabaseDetector.DatabasesSql
|
|
-- Kode: src/SQLInsight.Collectors/DynamicsAx/AxDatabaseDetector.cs : AxDatabaseDetector.DatabasesSql
|
|
SELECT name, snapshot_isolation_state, is_read_committed_snapshot_on
|
|
FROM sys.databases
|
|
WHERE database_id > 4 AND state = 0 AND source_database_id IS NULL AND HAS_DBACCESS(name) = 1
|
|
ORDER BY name
|
|
GO
|
|
|
|
-- Variant: SQL Server 2005 og nyere: AX-genkendelsen i hver overvåget database (kun metadata; vist for AXDB)
|
|
-- Kilde: AxDatabaseDetector.BuildDetectionSql
|
|
-- Kode: src/SQLInsight.Collectors/DynamicsAx/AxDatabaseDetector.cs : AxDatabaseDetector.BuildDetectionSql(database) = AxSql.InDatabase(database, AxDetection.MetadataSql)
|
|
-- Agenten kører dette i hver overvåget database fra listen ovenfor (ikke *_model). Ret [AXDB] til den database, du vil tjekke.
|
|
EXEC [AXDB].sys.sp_executesql N'SET NOCOUNT ON; SET LOCK_TIMEOUT 2000; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT s.name, UPPER(t.name), UPPER(c.name), TYPE_NAME(c.system_type_id), c.precision, c.max_length,
|
|
ISNULL(HAS_PERMS_BY_NAME(QUOTENAME(s.name) + N''.'' + QUOTENAME(t.name), N''OBJECT'', N''SELECT''), 0),
|
|
(SELECT TOP 1 p.name FROM sys.database_principals p WHERE p.sid = SUSER_SID())
|
|
FROM sys.tables t
|
|
JOIN sys.schemas s ON s.schema_id = t.schema_id
|
|
JOIN sys.columns c ON c.object_id = t.object_id
|
|
WHERE UPPER(t.name) IN (N''SQLDICTIONARY'', N''DATAAREA'', N''SYSCONFIG'', N''BATCH'', N''BATCHJOB'', N''BATCHJOBHISTORY'', N''BATCHGROUP'', N''NUMBERSEQUENCETABLE'', N''NUMBERSEQUENCESCOPE'', N''NUMBERSEQUENCEREFERENCE'', N''SYSTEMSEQUENCES'', N''MODELELEMENT'')
|
|
ORDER BY s.name, t.name, c.column_id';
|
|
SET LOCK_TIMEOUT 5000; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
|
|
GO
|
|
|
|
-- -------------------------------------------------------------------------------------------------------------------
|
|
-- AxSequenceCollector: RecId pr. tabel og firma i AX-databasen (valgfri AX-læseadgang)
|
|
-- -------------------------------------------------------------------------------------------------------------------
|
|
|
|
-- Variant: Axapta 3.0 (skema bmssa, verificeret mod kundedata): RecId pr. tabel (SYSTEMSEQUENCES)
|
|
-- Kilde: AxSchemaAdapter.RecIds(3.0)
|
|
-- Kode: src/SQLInsight.Core/DynamicsAx/AxSchemaAdapter.cs : AxSchemaAdapter.RecIds(database) via AxSql.InDatabase
|
|
EXEC [AXDB].sys.sp_executesql N'SET NOCOUNT ON; SET LOCK_TIMEOUT 2000; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT TOP (500) s.[TABID], s.[DATAAREAID], CAST(s.[NEXTVAL] AS bigint), CAST(s.[MAXVAL] AS bigint), (SELECT TOP 1 d.[NAME] FROM [bmssa].[SQLDICTIONARY] d WHERE d.[TABLEID] = s.[TABID] AND d.[FIELDID] = 0) FROM [bmssa].[SYSTEMSEQUENCES] s WHERE s.[NAME] = N''SEQNO'' ORDER BY s.[NEXTVAL] DESC';
|
|
SET LOCK_TIMEOUT 5000; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
|
|
GO
|
|
|
|
-- Variant: AX 4.0 (skema dbo) - ikke verificeret mod kundedata: RecId pr. tabel (SYSTEMSEQUENCES)
|
|
-- Kilde: AxSchemaAdapter.RecIds(4.0)
|
|
-- Kode: src/SQLInsight.Core/DynamicsAx/AxSchemaAdapter.cs : AxSchemaAdapter.RecIds(database) via AxSql.InDatabase
|
|
EXEC [AXDB].sys.sp_executesql N'SET NOCOUNT ON; SET LOCK_TIMEOUT 2000; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT TOP (500) s.[TABID], s.[DATAAREAID], CAST(s.[NEXTVAL] AS bigint), CAST(s.[MAXVAL] AS bigint), (SELECT TOP 1 d.[NAME] FROM [dbo].[SQLDICTIONARY] d WHERE d.[TABLEID] = s.[TABID] AND d.[FIELDID] = 0) FROM [dbo].[SYSTEMSEQUENCES] s WHERE s.[NAME] = N''SEQNO'' ORDER BY s.[NEXTVAL] DESC';
|
|
SET LOCK_TIMEOUT 5000; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
|
|
GO
|
|
|
|
-- Variant: AX 2009 - baseret på Microsofts dokumenterede skema, ikke verificeret mod kundedata: RecId pr. tabel (SYSTEMSEQUENCES)
|
|
-- Kilde: AxSchemaAdapter.RecIds(2009)
|
|
-- Kode: src/SQLInsight.Core/DynamicsAx/AxSchemaAdapter.cs : AxSchemaAdapter.RecIds(database) via AxSql.InDatabase
|
|
EXEC [AXDB].sys.sp_executesql N'SET NOCOUNT ON; SET LOCK_TIMEOUT 2000; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT TOP (500) s.[TABID], s.[DATAAREAID], CAST(s.[NEXTVAL] AS bigint), CAST(s.[MAXVAL] AS bigint), (SELECT TOP 1 d.[NAME] FROM [dbo].[SQLDICTIONARY] d WHERE d.[TABLEID] = s.[TABID] AND d.[FIELDID] = 0) FROM [dbo].[SYSTEMSEQUENCES] s WHERE s.[NAME] = N''SEQNO'' ORDER BY s.[NEXTVAL] DESC';
|
|
SET LOCK_TIMEOUT 5000; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
|
|
GO
|
|
|
|
-- Variant: AX 2012 RTM/R2/R3 - baseret på Microsofts dokumenterede skema, ikke verificeret mod kundedata: RecId pr. tabel (SYSTEMSEQUENCES)
|
|
-- Kilde: AxSchemaAdapter.RecIds(2012)
|
|
-- Kode: src/SQLInsight.Core/DynamicsAx/AxSchemaAdapter.cs : AxSchemaAdapter.RecIds(database) via AxSql.InDatabase
|
|
EXEC [AXDB].sys.sp_executesql N'SET NOCOUNT ON; SET LOCK_TIMEOUT 2000; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT TOP (500) s.[TABID], s.[DATAAREAID], CAST(s.[NEXTVAL] AS bigint), CAST(s.[MAXVAL] AS bigint), (SELECT TOP 1 d.[NAME] FROM [dbo].[SQLDICTIONARY] d WHERE d.[TABLEID] = s.[TABID] AND d.[FIELDID] = 0) FROM [dbo].[SYSTEMSEQUENCES] s WHERE s.[NAME] = N''SEQNO'' ORDER BY s.[NEXTVAL] DESC';
|
|
SET LOCK_TIMEOUT 5000; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
|
|
GO
|
|
|