5

SQL Server tracks page allocation in various internal bitmaps. Among these are the Global Allocation Map (GAM) and Page Free Space (PFS) pages. We know that GAM pages occur at set intervals of 511232 pages and that PFS pages occur at set intervals of 8088 pages.

Given a large enough data file this repetition will eventually lead to one page being next in line for both GAM and PFS. Doing the math, this happens after 1,011 GAMs or 63,904 PFS pages at page number 516,855,552. This equates to a single OS file of just under 4TB. As the maximum size of a single data file is 16TB (source) this has been allowed for.

My question: when a single data file reaches 4TB which page type is page 516855552 - GAM or PFS? Where does the the other one go? This comment from Paul Randal suggests it is shunted to one of the otherwise unused pages in the GAM extent:

GAM extents except the first one have GAM, SGAM, DIFF_MAP, ML_MAP. Every 4TB the GAM extent will also have a PFS page.

I've found this referenced here but not categorically explained:

-- There may be an issue with the ML map page position
-- on the four extents where PFS pages and GAM pages live
-- (at page IDs 516855552, 1033711104, 1550566656, 2067422208)
-- but I think we'll be ok.

No other meaningful results show for me on the googles.

The databases I have access to have several files so none meets the size requirement.

1 Answer 1

6

On a newly-created SQL Server 2022 database, the script below shows page 516855552 is a PFS page and 516855553 is a GAM page.

USE master;
DROP DATABASE IF EXISTS GAMTest;
CREATE DATABASE GAMTest
 ON  PRIMARY 
( NAME = N'GAMTest', FILENAME = N'H:\SqlFiles\GAMTest.mdf' , SIZE = 5TB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'GAMTest_log', FILENAME = N'G:\SqlFiles\GAMTest_log.ldf' , SIZE = 1GB , MAXSIZE = 1GB );
GO

USE GAMTest;

SELECT file_id, page_id, page_info.page_type_desc
FROM (VALUES(516855552),(516855553)) AS page_numbers(page_number)
CROSS APPLY sys.dm_db_page_info(DB_ID(), 1, page_numbers.page_number, 'DETAILED') AS page_info;
GO

Your Answer

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

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