Scripts I use for database space metrics on our SQL Server do not return the correct values for allocated_extent_page_count and unallocated_extent_page_count from sys.dm_db_file_space_usage when run against our SQL Server 2019 (RTM - 15.0.2000.5 (X64) Developer Edition (64-bit)) ) instances.
They work correctly with SQL Server 2016 (SP2-CU5 - 13.0.5264.1 (X64) Developer Edition) and SQL Server 2017 (RTM-CU12 - 14.0.3045.24 (X64) Developer Edition (64-bit)).
I first noticed this on databases where we had a large partitioned table that used VARCHAR(MAX) columns with large strings so I thought it may have been related to partition maintenance or LOB_DATA allocation units being ignored but I've stripped the test script down to the below and it is still a problem.
USE [master]
GO
IF DB_ID('testdmv') IS NOT NULL
BEGIN
ALTER DATABASE [TestDmv] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [TestDmv];
END
GO
CREATE DATABASE [TestDmv] ON PRIMARY
( NAME = N'TestDmv', FILENAME = N'D:\TestDmv.mdf' , SIZE = 5120MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024MB )
LOG ON ( NAME = N'TestDmv_log', FILENAME = N'D:\TestDmv_log.ldf' , SIZE = 1024MB , MAXSIZE = 2048GB , FILEGROWTH = 1024MB )
COLLATE SQL_Latin1_General_CP1_CI_AS WITH CATALOG_COLLATION = DATABASE_DEFAULT
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [TestDmv].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE [TestDmv] SET ANSI_NULL_DEFAULT OFF;
ALTER DATABASE [TestDmv] SET ANSI_NULLS OFF;
ALTER DATABASE [TestDmv] SET ANSI_PADDING OFF;
ALTER DATABASE [TestDmv] SET ANSI_WARNINGS OFF;
ALTER DATABASE [TestDmv] SET ARITHABORT OFF;
ALTER DATABASE [TestDmv] SET AUTO_CLOSE OFF;
ALTER DATABASE [TestDmv] SET AUTO_SHRINK OFF;
ALTER DATABASE [TestDmv] SET AUTO_UPDATE_STATISTICS ON;
ALTER DATABASE [TestDmv] SET CURSOR_CLOSE_ON_COMMIT OFF;
ALTER DATABASE [TestDmv] SET CURSOR_DEFAULT GLOBAL;
ALTER DATABASE [TestDmv] SET CONCAT_NULL_YIELDS_NULL OFF;
ALTER DATABASE [TestDmv] SET NUMERIC_ROUNDABORT OFF;
ALTER DATABASE [TestDmv] SET QUOTED_IDENTIFIER OFF;
ALTER DATABASE [TestDmv] SET RECURSIVE_TRIGGERS OFF;
ALTER DATABASE [TestDmv] SET DISABLE_BROKER;
ALTER DATABASE [TestDmv] SET AUTO_UPDATE_STATISTICS_ASYNC OFF;
ALTER DATABASE [TestDmv] SET DATE_CORRELATION_OPTIMIZATION OFF;
ALTER DATABASE [TestDmv] SET TRUSTWORTHY OFF;
ALTER DATABASE [TestDmv] SET ALLOW_SNAPSHOT_ISOLATION OFF;
ALTER DATABASE [TestDmv] SET PARAMETERIZATION SIMPLE;
ALTER DATABASE [TestDmv] SET READ_COMMITTED_SNAPSHOT OFF;
ALTER DATABASE [TestDmv] SET HONOR_BROKER_PRIORITY OFF;
ALTER DATABASE [TestDmv] SET RECOVERY SIMPLE;
ALTER DATABASE [TestDmv] SET MULTI_USER;
ALTER DATABASE [TestDmv] SET PAGE_VERIFY CHECKSUM;
ALTER DATABASE [TestDmv] SET DB_CHAINING OFF;
ALTER DATABASE [TestDmv] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF );
ALTER DATABASE [TestDmv] SET TARGET_RECOVERY_TIME = 60 SECONDS;
ALTER DATABASE [TestDmv] SET DELAYED_DURABILITY = DISABLED;
ALTER DATABASE [TestDmv] SET QUERY_STORE = OFF;
ALTER DATABASE [TestDmv] SET READ_WRITE;
GO
USE [TestDmv]
GO
---------------------------------------------
SET NOCOUNT ON;
GO
GO
CREATE OR ALTER PROCEDURE dbo.GetSpaceComparison
AS
SELECT 'FileProperty' AS [MetricSource],
SUM(CAST(DF.size / 128.0 AS Dec(9,1))) AS [SizeMB],
SUM(CAST(FILEPROPERTY(df.name, 'SpaceUsed') / 128.0 AS Dec(9,1))) AS [UsedMB],
SUM(CAST((df.size - FILEPROPERTY(name, 'SpaceUsed')) / 128.0 AS Dec(9,1))) AS [FreeMB]
FROM TestDmv.sys.database_files AS df
WHERE df.type_desc = 'ROWS'
GROUP BY df.type_desc
UNION ALL
SELECT 'dmv' AS [MetricSource],
SUM(CAST(total_page_count * 8 / 1024.0 AS Dec(9,1))) AS [SizeMB],
SUM(CAST(allocated_extent_page_count * 8 / 1024.0 AS Dec(9,1))) AS [UsedMB],
SUM(CAST(unallocated_extent_page_count * 8 / 1024.0 AS Dec(9,1))) AS [FreeMB]
FROM TestDmv.sys.dm_db_file_space_usage
ORDER BY [MetricSource]
GO
SET NOCOUNT ON;
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Test](
[Id] [bigint] NOT NULL,
[EntryTime] [datetime2](2) NOT NULL,
[BigCol1] [varchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[BigCol2] [varchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, /* OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF, */ DATA_COMPRESSION = PAGE) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Test] SET (LOCK_ESCALATION = AUTO)
GO
CREATE NONCLUSTERED INDEX [IX_Test_EntryTime] ON [dbo].[Test]
( [EntryTime] ASC,
[Id] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, /*OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF, */ DATA_COMPRESSION = PAGE) ON [PRIMARY]
GO
CREATE SEQUENCE [dbo].[TestId] AS [bigint]
START WITH 1 INCREMENT BY 1 MINVALUE -9223372036854775808 MAXVALUE 9223372036854775807 CACHE 1000
GO
PRINT '------DB Space Status before data load------'
EXEC dbo.GetSpaceComparison;
GO
SET NOCOUNT ON ;
DECLARE @SQL VARCHAR(MAX),
@i INT = 0,
@Id BIGINT,
@ServerUTCDatetime DATETIME2(2);
DECLARE @c1 VARCHAR(4000)= '',
@c2 VARCHAR(4000)= ''
WHILE LEN(@C1) < 4000
BEGIN
SET @C1 += CAST(NEWID() AS VARCHAR(36))
END
WHILE LEN(@C2) < 4000
BEGIN
SET @C2 += CAST(NEWID() AS VARCHAR(36))
END
SET @i = 0
WHILE @i <100000
BEGIN
SET @i += 1
SET @Id = NEXT VALUE FOR dbo.TestId;
SET @ServerUTCDatetime = SYSUTCDATETIME();
INSERT INTO dbo.Test (Id, EntryTime, BigCol1, BigCol2)
VALUES (@Id, @ServerUTCDatetime, @C1, @C2)
END
CHECKPOINT;
PRINT '
------DB Space Status after initial data load------'
EXEC dbo.GetSpaceComparison;
EXEC sp_spaceused;
SELECT type_desc,
CAST(SUM(total_pages) * 8 / 1024.0 AS DEC(9, 1)) AS [total_mb],
CAST(SUM(used_pages) * 8 / 1024.0 AS DEC(9, 1)) AS [used_mb],
CAST(SUM(data_pages) * 8 / 1024.0 AS DEC(9, 1)) AS [data_mb]
FROM sys.allocation_units
GROUP BY type_desc
ORDER BY type_desc
