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'

[olahallengren]

I am not able to reproduce this issue.

Does anyone have a repro with the latest version of IndexOptimize and the latest Service Pack and Cumulative Update Package?

[CommanderBond]

Yes, this happens since IndexOptimize is typically in another database than the objects to be stats-updated. If it touches in-memory objects there, it won't work because more than one database involved (db where IndexOptimize was created and the user database to be updated).

We had to exclude in-memory objects as described above as a hotfix. Maybe there is a better solution though.

[krzysiek250780]

solution provided by
clambrechts commented on Oct 19, 2024
works for me