Update stats fails on memory optimized tables in sql server 2016 sp2 CU7 with the same error 41317

[senthilnathanayyanar]

Description of the issue Update stats fails on memory optimized tables in sql server 2016 sp2 CU7 with the same error 41317. Verified https://github.com/olahallengren/sql-server-maintenance-solution/issues/154 but the instance is with latest patch.

SQL Server version and edition Microsoft SQL Server 2016 (SP2-CU7) (KB4495256) - 13.0.5337.0 (X64) May 16 2019 02:24:21 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor)

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: 2019-06-14 00:05:34 //--

Date and time: 2019-07-18 09:01:35 Server: XXXXX Version: 13.0.5337.0 Edition: Enterprise Edition: Core-based Licensing (64-bit) Platform: Windows Procedure: [XXXXXX].[dbo].[IndexOptimize] Parameters: @databases = 'USER_DATABASES, -XXXXXX', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @MinNumberOfPages = 1000, @MaxNumberOfPages = NULL, @SortInTempdb = 'Y', @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 = NULL, @delay = NULL, @WaitAtLowPriorityMaxDuration = NULL, @WaitAtLowPriorityAbortAfterWait = NULL, @Resumable = 'N', @AvailabilityGroups = NULL, @LockTimeout = NULL, @LockMessageSeverity = 16, @DatabaseOrder = NULL, @DatabasesInParallel = 'N', @LogToTable = 'Y', @execute = 'Y' Version: 2019-04-28 16:40:00 Source: https://ola.hallengren.com Date and time: 2019-07-18 09:01:35 Database: [bl] Status: ONLINE Standby: No Updateability: READ_WRITE User access: MULTI_USER Is accessible: Yes Recovery model: FULL Availability group: xxxxxxxxxxx Availability group role: PRIMARY Date and time: 2019-07-18 09:01:35 Command: UPDATE STATISTICS [bl].[dbo].[balance_entry] [idx_account_currency] Comment: ObjectType: Table, IndexType: Index, IndexType: NonClustered, Incremental: N, RowCount: 2999962, ModificationCounter: 3266 Msg 50000, Level 16, State 1, Procedure CommandExecute, Line 207 [Batch Start Line 0] Msg 41317, 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. Outcome: Failed Duration: 00:00:00 Date and time: 2019-07-18 09:01:35 Date and time: 2019-07-18 09:01:35 Command: UPDATE STATISTICS [bl].[dbo].[balance_entry] [PK__balance___3213E83EB6A4B846] Comment: ObjectType: Table, IndexType: Index, IndexType: NonClustered, Incremental: N, RowCount: 2296811, ModificationCounter: 701193 Msg 50000, Level 16, State 1, Procedure CommandExecute, Line 207 [Batch Start Line 0] Msg 41317, 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. Outcome: Failed Duration: 00:00:00 Date and time: 2019-07-18 09:01:35

Things in interest:

 1)The SQL version is 13.0.5337.0 which is the latest.(SQL 2016 SP2 CU7)  2)Server audit enabled on the server(Disk)  3)Error 41317 getting generated only on the in-memory table while on update stats  4)The error generates only with the ollahallengs script but native maintenance update stats completed successfully without any issue.

[olahallengren]

Is IndexOptimize located in the master database or in a user database?

Could you try executing UPDATE STATISTICS [bl].[dbo].[balance_entry] [PK__balance___3213E83EB6A4B846] from the same database as where you have the IndexOptimize stored procedure?

[olahallengren]

Could you also share your SQL Server Audit configuration?

[senthilnathanayyanar]

The manual execution of update stats from the database where indexoptimize stored procedure exist completed successfully without any issue.

Below is the audit configuration"

USE [master] GO

CREATE SERVER AUDIT [LogToFile] TO FILE ( FILEPATH = N'E:\SQL_Audit\' ,MAXSIZE = 200 MB ,MAX_ROLLOVER_FILES = 10 ,RESERVE_DISK_SPACE = ON ) WITH ( QUEUE_DELAY = 1000 ,ON_FAILURE = CONTINUE ,AUDIT_GUID = '489d0f38-0622-4322-bc57-e2337bdebf54' ) ALTER SERVER AUDIT [LogToFile] WITH (STATE = ON) GO

[matswestbo]

Looks to work when editing [dbo].[CommandExecute] to use "EXEC sp_executesql @Command" instead of EXECUTE(@Command) on line 197 and 206.

@olahallengren will this be okay for the rest of your scripts/functionallities? :)

Per now: Our workaround is to disable audit, run index maintenance then enable audit again

[senthilnathanayyanar]

Yes, i didnt see any other issues so far.

Verified the workaround and it is working fine . currently i am using the native maintainance job for the database , excluded the database using -DB_NAME.

[TheLowox]

Unfortunately the same problem exists in SQL Server 2019 CU8. Without Auditing enabled.

I just upgraded IndexOptimize+CommandExecute on my Quality Systems from an old version ( from 2018). The old Version could handle Memory Optimized Databases just fine. Now It throws the error...

Looks like I´m going back to the old version because I had no problems with that.

Last message it prints is that the DB is accessible and than the error:

If the update statistics /rebuild index statements would be executed in the destination database there would be no problem. Just a simple [USE][DB] before executing the first statement in a db would fix that.

I would ask for a pull request and fix it myself if I would understand the awesomeness it these scripts :D

[MykhailoPa]

Found that it is failed on env where tempdb metadata memory-optimized feature is enabled failed part is SELECT * FROM sys.stats stats OUTER APPLY sys.dm_db_incremental_stats_properties(stats.object_id, stats.stats_id) dm_db_incremental_stats_properties

if feature disabled - no issues