question

MSTechie-7364 avatar image
0 Votes"
MSTechie-7364 asked AmeliaGu-msft commented

Monitor in SQLServer Free Space with Available Disk for db Log files

For my SQLServer , how to Monitor free space in database log files after considering the available disk size

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi MSTechie-7364,

Did the answer help you?
Please feel free to let us know if you have any other question. If you find any post in the thread is helpful, you could kindly accept it as answer.

Best Regards,
Amelia

0 Votes 0 ·

1 Answer

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

Hi MSTechie-7364,
Please refer to the query which might be helpful:

 ;WITH src AS
 (
   SELECT 
          FileID      = f.file_id,
          LogicalName = f.name,
          [Path]      = f.physical_name, 
          FileSizeMB  = f.size/128.0, 
          UsedSpaceMB = CONVERT(bigint, FILEPROPERTY(f.[name], 'SpaceUsed'))/128.0, 
          GrowthMB    = CASE f.is_percent_growth WHEN 1 THEN NULL ELSE f.growth/128.0 END,
          MaxSizeMB   = NULLIF(f.max_size, -1)/128.0,
          DriveSizeMB = vs.total_bytes/1048576.0,
          DriveFreeMB = vs.available_bytes/1048576.0
   FROM sys.database_files AS f
   CROSS APPLY sys.dm_os_volume_stats(DB_ID(), f.file_id) AS vs
 )
 SELECT  FileID, LogicalName, [Path],
   FileSizeMB  = CONVERT(decimal(18,2), FileSizeMB),
   FreeSpaceMB = CONVERT(decimal(18,2), FileSizeMB-UsedSpaceMB),
   [%]         = CONVERT(decimal(5,2), 100.0*(FileSizeMB-UsedSpaceMB)/FileSizeMB),
   GrowthMB    = COALESCE(RTRIM(CONVERT(decimal(18,2), GrowthMB)), '% warning!'),
   MaxSizeMB   = CONVERT(decimal(18,2), MaxSizeMB),
   DriveSizeMB = CONVERT(bigint, DriveSizeMB),
   DriveFreeMB = CONVERT(bigint, DriveFreeMB),
   [%]         = CONVERT(decimal(5,2), 100.0*(DriveFreeMB)/DriveSizeMB)
 FROM src

Best Regards,
Amelia

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.