Azure SQL Database throwing error 1132 “The elastic pool has reached its storage limit” error, even if it's not full

All we need is an easy explanation of the problem, so here it is.

I’m trying to execute the following (sample) batch in an Azure SQL Database inside an Elastic Pool:

drop table if exists [dbo].[InsertTest];
create table [dbo].[InsertTest] (
    [id] uniqueidentifier,
    [filler] nvarchar(max)
);

insert into [dbo].[InsertTest] ([id], [filler])
select top 1000 newid(), replicate('X', 2048)
from [sys].[objects] as [T1]
    cross join [sys].[objects] as [T2];

/* drop table if exists [dbo].[InsertTest]; */

But the insert statement fails with the error message:

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

I tried to see if I ran out of space by running the query

select
    [type_desc]         as [file_type],
    [size] / 128        as [size_in_MB],
    [max_size] / 128    as [max_size_in_MB]
from [sys].[database_files];

but it seems that I still have space left.

file_type size_in_MB max_size_in_MB
ROWS 320 256000
LOG 584 1048576
FILESTREAM 0 0

What can I do about it? Am I missing something?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

The error message refers to the elastic pool so the problem might relate to another database than the current one. You can retrieve the information about the elastic pool using the following DMV query vs the Master database

SELECT TOP 5 avg_storage_percent / 100.0 * elastic_pool_storage_limit_mb AS ElasticPoolDataSpaceUsedInMB, * 
FROM sys.elastic_pool_resource_stats

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply