February 6, 2020 at 4:24 am
I actually never thought about this until today where i have been noticing significant drops in memory every few hours. When executing restore verifyonly on a back up file does it actually read the back up file and dump it into buffer pool ?
February 6, 2020 at 9:58 am
Well, it certainly reads the backup file, but what pages does it read? In a totally unscientific test, I ran a RESTORE VERIFYONLY and then looked at what types of pages were in the buffer pool. During normal operation you'd expect there to be mostly data pages and index pages, but on this occasion, the pages were mostly PFS (page free space) pages in tempdb. There were 626 such pages for a backup of size 10GB. The op_history column may not be available in SQL Server 2016 (it was introduced in CU15 of SQL Server 2017 and I don't know whether it was back-ported), so you may have to take my word for this!
RESTORE VERIFYONLY FROM DISK = 'X:\MyBackupFolder\MyBackupFile.BAK';
SELECT
database_id
,page_type
FROM sys.dm_os_buffer_descriptors
WHERE op_history = 0x000000000000015A; -- only the most recently added pages
John
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply