Azure SQL Server Error : The elastic pool has reached its storage limit. The storage usage for the elastic pool cannot exceed (204800)

Question

When I am trying to update or insert multiple rows from the application or directly sql server database, I am getting the error as below.

Msg 1132, Level 16, State 1, Line 1 The elastic pool has reached its storage limit. The storage usage for the elastic pool cannot exceed (204800) MBs.

I do not know how to handle this. Please help.

Answer 1

You should proactively check the current size quota for your databases, to make sure it is set as expected. To do this, the following statement can be used in the context of the target database:

SELECT DATABASEPROPERTYEX(DB_NAME(), 'MaxSizeInBytes');

To solve this issue scale up to the service objective with a larger maximum size quota, explicitly change the quota to match the maximum by using the ALTER DATABASE … MODIFY (MAXSIZE = …) command as shown above (unless a lower quota is desired to guarantee being able to scale down in the future). The change is executed in an online manner.

ALTER DATABASE DB1 MODIFY (MAXSIZE = 10 GB);

On this documentation you will find a table that shows the resources available at each service tier, including the maximum storage.

Answer 2

This was straightforward to fix in the Azure Portal. I selected the Elastic Pool and navigated to Settings > Configure. There is a slider where you can increase the size of the data. I doubled this for a small increase in cost and it fixed the issue.

Answer 3

Error when inserting data: "The elastic pool has reached its storage limit. The storage usage for the elastic pool cannot exceed (153600) MBs"

A scale up scale down of the database didn't fix the error.

Pool was scaled from 100GB to 150GB. Size stated in error is 153.6GB. Scaled the elastic pool to 250GB. Data successfully inserted. Scaled back down to 100GB, for cost purposes, and insert still worked.

A scale up of the elastic pool--beyond the limit in error message--and scale down fixed the issue.

Answer 4

Shrinking the database could be treated as intermediate solution, as this will release allocated space.

DBCC SHRINKDATABASE ('DB-Name', 10);