How to shrink log file for Azure SQL Database?
0
votes
2
answers
407
views
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:
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?
Asked by Greg
(582 rep)
Oct 16, 2024, 09:53 PM
Last activity: May 17, 2025, 04:06 AM
Last activity: May 17, 2025, 04:06 AM