CODEWITHSUNDEEP
sqlmemory
ao_DBA
ao_DBA

Reputation: 11

Ola Hallengren UPDATE STATS is failing for in memory tables SQL Server 2016

I am getting the below errors when I try to update STATS on a SQL Server 2016 in-memory tables. All other NON in-memory tables run fine.

Date and time: 2016-11-28 11:31:39
Command: UPDATE STATISTICS [DBServer101].[dbo].[tblProcess] [ix_tblProcess_InProcessId]
Msg 50000, Level 16, State 1, Procedure CommandExecute, Line 152 [Batch Start Line 4]
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: 2016-11-28 11:31:39

Here is the Ola Hallengren script parameters that I run:

EXECUTE [dbo].[IndexOptimize] 
@Databases = 'USER_DATABASES'
, @FragmentationLow = NULL 
, @FragmentationMedium = NULL 
, @FragmentationHigh = NULL 
, @UpdateStatistics = 'ALL'
, @OnlyModifiedStatistics = 'Y'
, @SortInTempdb = 'N'
, @LogToTable = 'N'

Any help is greatly appreciated.

Upvotes: 1

Views: 1125

Answers (4)

Dan Shargel
Dan Shargel

Reputation: 1

I got it to work for my by going through the latest version (12/2022) of IndexOptimize and adding "AND tables.is_memory_optimized = 0" in the three applicable places in the build of @CurrentCommand.

Upvotes: 0

Wilfred van Dijk
Wilfred van Dijk

Reputation: 99

I also bumped into this issue. As a temporary fix, I removed the indextype 7 references in the stored procedure

Upvotes: 0

donger
donger

Reputation: 11

I did a slight different approach. I kept the @UpdateStatistics='ALL' for Ola Hallengren script, then I added another step to re-run the statistics failed from the corresponding DBs using Ola's commandlog table.

USE master;
DECLARE @sqlcmd VARCHAR(MAX)='';
WITH cteRerun AS (
SELECT DISTINCT DatabaseName, Command
FROM DBATools.dbo.CommandLog
WHERE ErrorNumber<>0 AND StartTime>DATEADD(HOUR,-12,GETDATE())
)
SELECT @sqlcmd+='
USE '+QUOTENAME(DatabaseName)+';
'+Command+';'
FROM cteRerun
ORDER BY DatabaseName,Command;

EXEC (@sqlcmd);

Upvotes: 1

Andre Porter
Andre Porter

Reputation: 21

Take a look at the following article on unsupported featured for In-Memory OLTP. https://msdn.microsoft.com/en-us/library/dn133181.aspx

It mentions that "Cross-database queries and transactions are not supported". I contacted Ola to make sure he was aware of the error, hopefully it'll be corrected in subsequent versions.

If you haven't already resolved the problem, what you can do is perform your Index & Statistics maintenance separately. For Index Maintenance, we changed the @UpdateStaistics parameter to NULL in order to resolve the In-Memory error with cross DB transactions. And we're performing Update Stats outside of dbo.IndexOptimize and directly from each database (avoid cross DB transaction this works).

--THIS WILL BREAK BECAUSE dbo.IndexOptimize RESIDES IN A DBA DATABASE
USE DBA_Utility
GO
EXECUTE dbo.IndexOptimize 
    @Databases = 'WideWorldImporters', 
    @FragmentationLow =  NULL, 
    @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationLevel1 = 5,
    @FragmentationLevel2 = 30,
    @UpdateStatistics = 'ALL',
    @OnlyModifiedStatistics = 'Y',
    @LogToTable = 'Y',
    @TimeLimit = 21600
GO

--THIS WILL WORK 
USE [WideWorldImporters]
GO
UPDATE STATISTICS [WideWorldImporters].[Warehouse].[ColdRoomTemperatures] [IX_Warehouse_ColdRoomTemperatures_ColdRoomSensorNumber]

Upvotes: 2

Related Questions