如何在Azure SQL Database中修改MAXSIZE？原SQL Server语句报错

Ah, I get it—you ran into a common gotcha with Azure SQL Database vs. on-premises SQL Server. The thing is, Azure SQL DB is a Platform-as-a-Service (PaaS) offering, so it manages underlying file storage differently than your local SQL Server instance. You can't directly modify the log file's MAXSIZE like you do on-prem because Azure handles file-level management entirely for you.

Let me break down the correct ways to adjust storage limits (which indirectly controls log growth) based on your Azure SQL DB service model:

For vCore-based databases #

vCore gives you granular control over storage, but you still don’t target individual log files. Instead, you set the database-wide MAXSIZE, which caps the total combined storage (data + logs) for the database. The log will automatically grow within this limit as your workload requires.

Use this syntax to update the database's total storage cap:

ALTER DATABASE [YourDBName] MODIFY (MAXSIZE = 1024 GB); -- Adjust the value to your needs

You can set MAXSIZE up to the maximum allowed for your vCore tier (e.g., General Purpose supports up to 4TB, Business Critical up to 8TB). Azure will handle log file expansion automatically within this total storage limit.

For DTU-based databases #

DTU databases are tied to predefined service tiers (Basic, Standard, Premium), each with fixed maximum storage limits. To adjust MAXSIZE, you first need to ensure your service tier supports the size you want, then modify both the tier and MAXSIZE in a single statement.

For example, to upgrade to the Standard S1 tier (which allows up to 250GB) and set the corresponding MAXSIZE:

ALTER DATABASE [YourDBName] MODIFY (
    EDITION = 'Standard',
    SERVICE_OBJECTIVE = 'S1',
    MAXSIZE = 250 GB
);

Keep these tier-specific limits in mind:

• Basic tier maxes out at 2GB
• Standard tiers (S0-S3) allow up to 250GB
• Premium tiers (P1-P6) support up to 1TB

If you try to set a MAXSIZE larger than your tier’s allowed limit, you’ll get an error—so make sure the tier and size align.

Key takeaway #

Azure SQL Database fully manages log file growth and retention behind the scenes. The log will expand automatically to meet workload demands, as long as it doesn’t hit the database’s overall MAXSIZE limit. You don’t need to configure log file MAXSIZE individually like you do on-prem; Azure takes care of that heavy lifting for you.

内容的提问来源于stack exchange，提问作者Michael Biniashvili