3

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?

5

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
1
  • I confirm my problem was related to the elastic pool. I don't have permissions to access the master database from my user, but from the Azure portal I can see the Elastic Pool storage is actually full. Thanks. – Davide De Pretto Apr 19 at 9:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.