SQL Server - Space Available

Question

I can't find exact answer on my question. When i right click on any database in SSMS, i see "Space available parameter". Is this free space in mdf file only or it include transaction log file too (free space in mdf + ldf files together)? Thanks

I asked because ... I have a table (size about 450GB with 5 millions rows) with LOB data. Application support delivered this script to delete some rows (should delete about 300K rows).

declare @cnt int;
declare @cnt_total int = 0;
set nocount off
while 1 = 1
begin
    delete top(5000)    -- PARAM BATCH_SIZE
    from fw.fw_o_clob
    where DT_TEC_CREATED < '2019-01-01T00:00:00.0000000'
        AND ID_CLOB <> -1   
    ;
    set @cnt = @@ROWCOUNT;
    set @cnt_total += @cnt;
    if @cnt = 0
        break;
end;
print concat('Total ', @cnt_total, ' rows deleted.');
go

But when we run this, "Size DB" started growing (info from properties) - this is OK, because transaction log started growing but what is interesting "Space available" was getting smaller and smaller so we canceled it. Can someone explain me why? Thanks

Answer 1

From looking at a Profiler trace of the SSMS dialog opening the queries are essentially:

Size:

SELECT sum(df.size) * 8 / 1024.0 Size_MB
FROM sys.database_files df;

and

Space Available:

with q as
(
    SELECT
    (SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
    (SUM(a.total_pages) + (SELECT ISNULL(SUM(CAST(df.size as bigint)), 0) FROM sys.database_files AS df WHERE df.type = 2 )) AS [SpaceUsed]
    FROM sys.partitions p 
    join sys.allocation_units a 
      on p.partition_id = a.container_id 
    left join sys.internal_tables it 
      on p.object_id = it.object_id
) 
select (DbSize - SpaceUsed) * 8 / 1024.0 SpaceAvailable_MB
from q;

So Size includes the log file, but Space Available does not. It's only the unallocated space in database files.