2

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
  • That's a lot of script. Could you reduce this to (a) a table that produces the problem and (b) the query that shows the wrong value(s) and (c) what you believe those values should be and why? – Aaron Bertrand Feb 27 '20 at 20:48
  • Also, please don't run RTM. Right now, you're missing over 200 fixes. – Aaron Bertrand Feb 27 '20 at 20:54
  • Thanks @AaronBertrand, from initial testing it seems that SQL 2019 CU2 has fixed the problem. I could not see any mention of the dmv in the CU notes but something must have been changed. I included the relatively long script as the table creation and dmv query alone do not indicate any problem by themselves and I needed the other code to reproduce the issue. I agree I could have still made it more concise though but wanted to provide all the DB settings in case they were relevant. – Ianwork Feb 28 '20 at 11:19
0

Applying SQL 2019 CU2, as suggested by Aaron Bertrand, has fixed the issue and now sys.dm_db_file_space_usage return the correct values for allocated_extent_page_count and unallocated_extent_page_count.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.