Could someone help shed some light on a strange sys.dm_db_file_page_usage result of a large database? sys.dm_db_file_space_usage result: |total_page_count | allocated_extent_page_count | unallocated_extent_page_count | |:----------------- |:---------------------------:|:----------------------------:| |15,632,168 | 295,104 | 15,337,064

I started looking into this dmv because Ola Hallengren DatabaseBackup sproc won't split files into multiple files with NumberOfFiles and MinBackupSizeForMultipleFiles parameter values defined. I usually use this to split backup files into 8 when database sizes are larger than 10GB:

Procedure: [master].[dbo].[DatabaseBackup] [SQLSTATE 01000]
Parameters: @Databases = 'USER_DATABASES', @Directory = 'X:\DatabaseBackupDir', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 192, @CleanupMode = 'AFTER_BACKUP', @Compress = NULL, @CopyOnly = 'N', @ChangeBackupType = 'N', @BackupSoftware = NULL, @CheckSum = 'Y', @BlockSize = NULL, @BufferCount = NULL, @MaxTransferSize = NULL, @NumberOfFiles = 8, @MinBackupSizeForMultipleFiles = 10240, @MaxFileSize = NULL, @CompressionLevel = NULL,...

However, on a recently acquired server, the database size was 100+GB but the script generated one large backup file of 46GB instead of 8 smaller files. Tracing through DatabaseBackup proc, it seems the following code might be the cause for backup file not splitting

          SET @CurrentCommand = 'SELECT @ParamAllocatedExtentPageCount = SUM(allocated_extent_page_count), @ParamModifiedExtentPageCount = SUM(modified_extent_page_count) FROM sys.dm_db_file_space_usage'
    EXECUTE @CurrentDatabase_sp_executesql @stmt = @CurrentCommand, @params = N'@ParamAllocatedExtentPageCount bigint OUTPUT, @ParamModifiedExtentPageCount bigint OUTPUT', @ParamAllocatedExtentPageCount = @CurrentAllocatedExtentPageCount OUTPUT, @ParamModifiedExtentPageCount = @CurrentModifiedExtentPageCount OUTPUT
SELECT BackupSize =  CASE WHEN @CurrentBackupType = 'FULL' THEN COALESCE(CAST(@CurrentAllocatedExtentPageCount AS bigint) * 8192, CAST(@CurrentDatabaseSize AS bigint) * 8192)
SELECT @CurrentNumberOfFiles = CASE WHEN ... WHEN @NumberOfFiles > 1 AND (BackupSize < MinBackupSizeForMultipleFiles) THEN NumberOfDirectories...

When a backup of the same database was restored to another server, allocated_extent_page_count was close to total_page_count of 15-million+ pages and backup were successfully splitted into 8 files.
Anyone has any pointer on what could cause allocated_extent_page_count to be so much lower than total_page_count on this server?


Your Answer

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