Azure SQL Database – Modify file failed – Size is greater than MAXSIZE.

This week I was working with a customer and when I tried to move one of the Azure SQL Database from the Elastic Poll I received the following message:

Code: DeploymentFailed

Message: At least one resource deployment operation failed. Please list deployment operations for details. Please see https://aka.ms/arm-debug for usage details.

Code: Conflict

Message: The resource operation completed with terminal provisioning state ‘Failed’.

Code: 45316

Message: MODIFY FILE failed. Size is greater than MAXSIZE. Please query sys.database_files and use DBCC SHRINKFILE to reduce the file size first.

This error occurs when the database size is larger than the tier that is selected allows. In the example below I am trying to remove database EDW from an Elastic Pool and I chose Tier Basic (5DTUS).

It is important to know the database size for each tier:

*The number above is as at 29th June 2017, from Azure Documentation.

Azure Portal may show a size for a database that should fit into the desired tier, but the actual database file in the backend may be over the limit of selected tier which can block the scaling.

Solution 1: Change the tier to a higher tier when you are removing the database from Elastic Pool, in the example above you could change it to Standard which supports up to 250 GB or Premium which supports up to 4TB.

You can use the query below to see the actual size of the database. The allocated size (MB) will be calculated into the database size on the backend during the operation, this means if the total sum is over the tier max size the operation will fail.

SELECT 

file_id, 
name as 'db_file', 
CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 as 'size(MB)', 
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 as 'allocated_size(MB)', 
size/128.0 as 'total_size(MB)' 

from 
sys.database_files;

Solution 2: If there is a large amount of allocated space and if reducing it would reduce the database size to the desired tier limit, you can try to shrink the database file.

DBCC SHRINKFILE(1, TRUNCATEONLY)

The script above will only reclaim space at the end of the file, so if after running this command the above space query does not show below the needed limit, you will need to rebuild the database file as the empty allocated space can be dispersed throughout the database file.

You can use the command below to rebuild the database, this command will rebuild the entire database file in the background.

DBCC SHRINKDATABASE(DatabaseName)

After running the command above, wait for a few minutes and check the database size again to confirm if the total database size is at the expected size.

Hope it helps you!