Sev1: Azure SQL Database reached its max size. Inserts fail with error 40544 and the app is read-only

#Symptoms

• Application errors: The database 'X' has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions. (error 40544).
• Reads work; INSERT, UPDATE, index rebuilds and sometimes logins that write session data fail.
• In an elastic pool: error 1132 (the pool has reached its storage limit) for every database in the pool.

#Step 1: confirm the limit (2 minutes)

-- in the user database
SELECT DATABASEPROPERTYEX(DB_NAME(), 'MaxSizeInBytes') / 1048576.0 AS max_mb,
       SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint)) * 8 / 1024.0 AS used_mb
FROM sys.database_files WHERE type_desc = 'ROWS';

az sql db show -g rg-data -s sql-prod -n appdb \
  --query "{maxGB:maxSizeBytes, sku:currentServiceObjectiveName, tier:sku.tier}" -o table

az monitor metrics list --resource $(az sql db show -g rg-data -s sql-prod -n appdb --query id -o tsv) \
  --metric storage_percent allocated_data_storage --interval PT5M --aggregation Maximum -o table

#Step 2: restore writes (5-15 minutes)

Raise max size if the current service objective allows more:

az sql db update -g rg-data -s sql-prod -n appdb --max-size 500GB

If the tier itself is the ceiling, scale up. This is an online operation, with a short disconnect at the end:

az sql db update -g rg-data -s sql-prod -n appdb --service-objective GP_Gen5_8 --max-size 1TB
az sql db op list -g rg-data -s sql-prod -n appdb -o table   # watch progress

Elastic pool:

az sql elastic-pool update -g rg-data -s sql-prod -n pool-prod --max-size 1TB
# or move the largest database out of the pool for now
az sql db update -g rg-data -s sql-prod -n bigdb --elastic-pool "" --service-objective GP_Gen5_4

Warning

Scaling a large database between some tiers (for example into or out of Hyperscale, or General Purpose to Business Critical) copies data and can take hours. Pick a change within the same tier for the emergency; plan tier moves separately.

#Step 3: find what grew

SELECT TOP 20 s.name + '.' + t.name AS table_name,
       SUM(a.used_pages) * 8 / 1024 AS used_mb,
       SUM(CASE WHEN i.index_id IN (0,1) THEN p.rows END) AS rows
FROM sys.tables t
JOIN sys.schemas s ON s.schema_id = t.schema_id
JOIN sys.indexes i ON i.object_id = t.object_id
JOIN sys.partitions p ON p.object_id = i.object_id AND p.index_id = i.index_id
JOIN sys.allocation_units a ON a.container_id = p.partition_id
GROUP BY s.name, t.name
ORDER BY used_mb DESC;

Typical culprits: an audit or log table nobody purges, a staging table a failed job never truncated, duplicated rows from a retry loop, or Query Store set to a very large size.

SELECT actual_state_desc, current_storage_size_mb, max_storage_size_mb FROM sys.database_query_store_options;

#Step 4: reclaim space

Delete in batches so the transaction log does not fill:

WHILE 1 = 1
BEGIN
  DELETE TOP (50000) FROM dbo.AppLog WHERE LoggedAt < DATEADD(day, -90, SYSUTCDATETIME());
  IF @@ROWCOUNT = 0 BREAK;
END

Or, if a whole table can go, TRUNCATE TABLE is minimal-logging and instant. Compress large tables:

ALTER TABLE dbo.AppLog REBUILD WITH (DATA_COMPRESSION = PAGE, ONLINE = ON);

Caution

Do not run DBCC SHRINKDATABASE during the incident. It is slow, fragments indexes and is not needed to restore writes; freed space inside the files is reused automatically.

#Step 5: confirm

• Write test from the application succeeds.
• storage_percent below 80% with headroom for at least a month of growth.
• Error 40544 count in application logs back to zero.

#Prevent it next time

Alert at 80% and 90% storage:

az monitor metrics alert create -g rg-data -n sql-appdb-storage-80 \
  --scopes $(az sql db show -g rg-data -s sql-prod -n appdb --query id -o tsv) \
  --condition "max storage_percent > 80" --window-size 15m --evaluation-frequency 5m \
  --action $(az monitor action-group show -g rg-mon -n ag-dba --query id -o tsv)

• Add retention jobs for log and audit tables, owned by the application team.
• Set max size deliberately rather than leaving the default, and review growth monthly.
• For fast-growing databases, consider Hyperscale, which grows storage automatically up to its much larger limit.