Share via

Possible to set the size of log file in VCore SQL Database?

WisonHii 81 Reputation points
2023-02-28T05:52:50.64+00:00

Currently, we use VCore licensed SQL Azure, with the Data Max Size of 4TB.

After querying the log size, it only uses less than 1 GB.

But we cann't configure the size of log file, it always be the 30% of Data Max Size, which is about 1.3 TB.

As you know, we should pay for the storage size separately in VCore licensed model. But actually, we don't need such a large log file.

Is there any possible method to configure the size of log file manually, then we can have a cost saving about 280$ every month.

SpaceUsedMB SpaceAllocatedMB MaxFileSizeMB
43 899 1048576
Azure SQL Database
Accepted answer
  1. ShaktiSingh-MSFT 14,406 Reputation points Microsoft Employee
    2023-03-02T06:00:11.2833333+00:00

    Hi @WisonHii ,

    I have checked with the internal team.

    It is suggested to use Hyperscale tier for cost saving.

    Please let us know if you have questions on this.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. ShaktiSingh-MSFT 14,406 Reputation points Microsoft Employee
    2023-02-28T09:23:58.9366667+00:00

    Hi @WisonHii ,

    Welcome to Microsoft Q&A forum and thanks for using Azure services.

    As I understand from the question, you want to know if it is Possible to set the size of log file in VCore SQL Database.

    Unlike data files, Azure SQL Database automatically shrinks transaction log file to avoid excessive space usage that can lead to out-of-space errors. It is usually not necessary for customers to shrink the transaction log file.

    In Premium and Business Critical service tiers, if the transaction log becomes large, it may significantly contribute to local storage consumption toward the maximum local storage limit. If local storage consumption is close to the limit, customers may choose to shrink transaction log using the DBCC SHRINKFILE command as shown in the following example. This releases local storage as soon as the command completes, without waiting for the periodic automatic shrink operation.

    The following example should be executed while connected to the target user database, not the master database.

    -- Shrink the database log file (always file_id 2), by removing all unused space at the end of the file, if any.
DBCC SHRINKFILE (2, TRUNCATEONLY);

    Here is the standard documentation link for Manage file space for databases in Azure SQL Database

    Hope this helps. If this answers your query, do click Accept Answer and Mark Helpful for the same. And, if you have any further query do let us know.