As per the title - modified_extent_page_count shows 0 extents modified on a 1.7TB database, but running a differential takes takes 4-5 hours and generates a 50GB file.

I wondered if anyone has come across a similar issue?

This flag was introducted in SQL 2017 and backported to SQL 2016 SP2. The server I am using is SQL 2016 SP2 CU17.

A full backup was created, reducing modified extents to 0. I would have assumed a differential straight after would complete quickly?

Query being run to check extents:

SELECT  DB_NAME() AS [database],
        SUM(allocated_extent_page_count) AS allocated_extent_page_count,
        SUM(modified_extent_page_count) AS modified_extent_page_count,
        CONVERT(DECIMAL(4,2),(SUM(modified_extent_page_count) * 100) / (SUM(allocated_extent_page_count) * 1.)) AS percent_changed
FROM sys.dm_db_file_space_usage


I've tested updating a handful of rows and modified extents did not increase - I wonder if extents had been modified in the background but not showing - possible bug. I will have a look to see if I can find one.

  • Please format your sql by placing 3 backticks on teh line before the sql statement and teh line after it. Feb 2 at 12:04


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.