Database scale error: The database cannot proceed with pricing-tier update as it has memory-optimized objects

First published on MSDN on Apr 04, 2017
You cannot scale SQL Azure databases from Premium to Standard with Memory optimized objects. You will get the error:

Database scale operation from Premium P1: 125 DTU, 500 GB to Standard S2: 50 DTU, 250 GB failed for <database name>. Error message: The database cannot proceed with pricing-tier update as it has memory-optimized objects. Please drop such objects and try again

Currently there are three different structures that could be memory optimized in SQL Azure: natively compiled stored procedures, tables, and user defined table types.

You can use the first query below to list all your memory optimized objects and the second to create drop statements for them all. Run the drop statements to remove the memory optimized objects so that you can scale down.

[code language="sql"]
--Display all Memory Optimized objects

select OBJECT_SCHEMA_NAME(object_id) as [schema], OBJECT_NAME(object_id) as [name], uses_native_compilation as is_memory_optimized, [type] = 'Natively compiled stored procedure'
from sys.sql_modules where uses_native_compilation = 1
union
select SCHEMA_NAME([schema_id]) AS [schema], [name], is_memory_optimized, [type] = 'Table'
from sys.tables where is_memory_optimized = 1
union
select SCHEMA_NAME([schema_id]) AS [schema], [name], is_memory_optimized, [type] = 'User Defined Table Type'
from sys.table_types where is_memory_optimized = 1
[/code]


[code language="sql"]
--Create Drop statements for memory optimized objects
select CONCAT('DROP PROCEDURE [', OBJECT_SCHEMA_NAME(object_id), '].[', OBJECT_NAME(object_id), '];')
from sys.sql_modules where uses_native_compilation = 1
union
select CONCAT('DROP TABLE [', SCHEMA_NAME([schema_id]), '].[', [name], '];')
from sys.tables where is_memory_optimized = 1
union
select CONCAT('DROP TYPE [', SCHEMA_NAME([schema_id]), '].[', [name], '];')
from sys.table_types where is_memory_optimized = 1
[/code]