index optimize procedure with update statistics on memory optimized tables fails

[BernaLudo]

Description of the issue
Date and time: 2024-09-19 09:30:44
Server: SQLServer\Instance1
Version: 16.0.4085.2
Edition: Enterprise Edition: Core-based Licensing (64-bit)
Platform: Windows
Procedure: [DbaTools].[dbo].[IndexOptimize]
Parameters: @databases = 'DB', @FragmentationLow = NULL, @FragmentationMedium = NULL, @FragmentationHigh = NULL, @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @MinNumberOfPages = 1000, @MaxNumberOfPages = NULL, @SortInTempdb = 'N', @MaxDOP = NULL, @FillFactor = NULL, @PadIndex = NULL, @LOBCompaction = 'Y', @UpdateStatistics = 'ALL', @OnlyModifiedStatistics = 'Y', @StatisticsModificationLevel = NULL, @StatisticsSample = NULL, @StatisticsResample = 'N', @PartitionLevel = 'Y', @MSShippedObjects = 'N', @indexes = NULL, @Timelimit = 600, @delay = NULL, @WaitAtLowPriorityMaxDuration = NULL, @WaitAtLowPriorityAbortAfterWait = NULL, @Resumable = 'N', @AvailabilityGroups = NULL, @LockTimeout = NULL, @LockMessageSeverity = 16, @StringDelimiter = ',', @DatabaseOrder = NULL, @DatabasesInParallel = 'N', @ExecuteAsUser = NULL, @LogToTable = 'N', @execute = 'Y'
Version: 2022-12-03 17:23:44
Source: https://ola.hallengren.com

Date and time: 2024-09-19 09:30:44
Database: [DB]
State: ONLINE
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Recovery model: SIMPLE
Is accessible: Yes

Msg 41317, Level 16, State 0, Line 1
A user transaction that accesses memory optimized tables or natively compiled modules cannot access more than one user database or databases model and msdb, and it cannot write to master.

Completion time: 2024-09-19T09:30:47.0307071+02:00

SQL Server version and edition
Execute SELECT @@VERSION
Version: 16.0.4085.2
Edition: Enterprise Edition: Core-based Licensing (64-bit)

Version of the script

--// Source: https://ola.hallengren.com //--
--// License: https://ola.hallengren.com/license.html //--
--// GitHub: https://github.com/olahallengren/sql-server-maintenance-solution //--
--// Version: 2022-12-03 17:23:44 //--

What command are you executing?
EXECUTE dbo.IndexOptimize
@databases = 'SentryOne',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@Timelimit = 600

What output are you getting?
Msg 41317, Level 16, State 0, Line 1
A user transaction that accesses memory optimized tables or natively compiled modules cannot access more than one user database or databases model and msdb, and it cannot write to master.

[R-dba]

Hi,
I have the same problem.
SQL Version 16.0.4131.2
IndexOptimize Version: 2022-12-03 17:23:44
IndexOptimize SP, CommandExecute SP are stored in the DB where I get the error msg.

Script call:
USE DB
GO
EXECUTE dbo.IndexOptimize

@databases = 'DB',

@FragmentationLow = NULL ,

@FragmentationMedium = NULL ,

@FragmentationHigh = NULL ,

@UpdateStatistics = 'ALL' ,

@OnlyModifiedStatistics = N'Y' ,

@LogToTable = N'Y';

Result:
Msg 41317, Level 16, State 0, Line 3
A user transaction that accesses memory optimized tables or natively compiled modules cannot access more than one user database or databases model and msdb, and it cannot write to master.

[clambrechts]

Hi,

It's necessary to add in code of optimize: "AND tables.is_memory_optimized = 0"

      IF @PartitionLevelStatistics = 1
      BEGIN
        SET @CurrentCommand = @CurrentCommand + ' OUTER APPLY sys.dm_db_incremental_stats_properties(stats.object_id, stats.stats_id) dm_db_incremental_stats_properties'
      END

      SET @CurrentCommand = @CurrentCommand + ' WHERE objects.[type] IN(''U'',''V'')'
                                                + CASE WHEN @MSShippedObjects = 'N' THEN ' AND objects.is_ms_shipped = 0' ELSE '' END
                                                + ' AND NOT EXISTS(SELECT * FROM sys.indexes indexes WHERE indexes.[object_id] = stats.[object_id] AND indexes.index_id = stats.stats_id) AND tables.is_memory_optimized = 0'