Sql-server – SQL Server – Space Available

sql serversql-server-2016

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

Best Answer

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.

Related Question