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'