0

My Azure Sql Database transaction log file is about 1 TB in size, but only 1 GB is in use:

SELECT file_id, name, type_desc,
CAST(FILEPROPERTY(name, 'SpaceUsed') AS decimal(19,4)) * 8 / 1024. AS space_used_mb,
CAST(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS decimal(19,4)) AS space_unused_mb,
CAST(size AS decimal(19,4)) * 8 / 1024. AS space_allocated_mb,
CAST(max_size AS decimal(19,4)) * 8 / 1024. AS max_size_mb
FROM sys.database_files;

Results:

enter image description here

I'm trying to cut it down to 50 GB in size, but neither of these seem to have any effect, the space_unused_mb and space_allocated_mb values remain the same:

DBCC SHRINKFILE (2, truncateonly);
DBCC SHRINKFILE (2, 50000);

Or am I barking up the wrong tree here? Does log file size count against storage cost?

Improve this question
1
  • From how the pricing calculator works looks like it is chargeable but just sized automatically based on the data size selected (a ~30% overhead on top of that). I doubt that the size is changeable by us
    – Martin Smith
    Commented Oct 17 at 8:10

2 Answers 2

Reset to default
0

To recover log space you can run the following statement.

DBCC SHRINKFILE (log, 0)

Setting the target size to 0 will shrink the log file as much as possible, but it won't shrink it below the size needed to store the active portion of the log. If you are using Azure SQL Hyperscale then shrinking is not a supported operation.

Improve this answer
3
  • if DBCC SHRINKFILE (2, 50000); doesn't work, why would DBCC SHRINKFILE (log, 0); work?
    – Greg
    Commented Oct 18 at 1:57
  • It has worked for me on some scenarios. Setting the target size to 0 will shrink the log file as much as possible, but it won't shrink it below the size needed to store the active portion of the log. If you are using Azure SQL Hyperscale then it won't work because shrink is not supported. As you can read here my suggestions do usually work stackoverflow.com/questions/45829322/…
    – Alberto Morillo
    Commented Oct 18 at 4:07
  • ok, I'm sing hyperscale, that's why it won't work.
    – Greg
    Commented Oct 20 at 23:25
0

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 to shrink the transaction log file.

DBCC SHRINKFILE (2, TRUNCATEONLY);

But the transaction log size may not be reducible until the next transaction log backup is completed.

It appears that the definitive solution is:

ALTER DATABASE <db> SET AUTO_SHRINK ON;

Usually, it is not a good option on prem but on Azure it is acceptable.

However, if you're utilizing Azure SQL Database in Hyperscale, the scenario differs as shrinking the database was not possible. Recently, Microsoft has introduced the capability to shrink databases in public preview. https://techcommunity.microsoft.com/t5/azure-sql-blog/public-preview-shrink-for-azure-sql-database-hyperscale/ba-p/4181976

Improve this answer
10
  • 1
    "Usually, it is not a good option on prem but on Azure it is acceptable." - Why?
    – J.D.
    Commented Oct 17 at 12:31
  • From my experience, manually managing the database size on-premises is a good practice. However, with an Azure SQL database, it can sometimes feel tedious, like trying to clear water from a beach. Having auto-shrink enabled is convenient for resolving issues and minimizing effort. You can at least try it, and if necessary, you can always revert back after some time.
    – MBuschi
    Commented Oct 17 at 14:14
  • I agree with MBuschi. This is the reason why @J.D. : jeeja.biz/2024/05/12/myth-busters-who-is-scared-of-auto_shrink
    – Francesco Mantovani
    Commented Oct 21 at 6:14
  • @FrancescoMantovani Sure, but that article is based on the premise "The cost of the SQL Elastic Pool doesn’t justify moving to it", when the cost of being in the cloud in general isn't justified itself. So it's not surprising using something previously known as bad practice to make something else like the cloud suck less. It's still all not great. And basic guidelines still apply such as when you've grown from 100 GB to 1 TB from a specific event, you can manually shrink for those one-off cases.
    – J.D.
    Commented Oct 21 at 12:27
  • @J.D. Both DBCC SHRINKFILE (log, 0) and DBCC SHRINKDATABASE (N'db1') are simply not working in an Azure SQL Database scenario. Have you tried them?
    – Francesco Mantovani
    Commented Oct 21 at 18:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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