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]; */
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.
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.
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 🙂