Dismiss Notice

Register now to be one of the first members of this SharePoint Community! Click here it just takes seconds!

Dismiss Notice
Welcome Guest from Country Flag

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

Discussion in 'Official Microsoft News' started by cabattag - MSFT, Apr 4, 2017.

Thread Status:
Not open for further replies.
  1. cabattag - MSFT

    cabattag - MSFT Guest

    Blog Posts:
    0
    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.


    --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



    --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

    Continue reading...
     
Thread Status:
Not open for further replies.

Share This Page

LiveZilla Live Chat Software