Mark S. Rasmussen improve.dk
Apr 28
2011

I often like to spend my weekends perusing the sys.system_internals_allocation_units table, looking for the remnants of Frodo and his crew. In the sys.system_internals_allocation_units there are several references to relevant pages:

select
	first_page,
	root_page,
	first_iam_page
from
	sys.system_internals_allocation_units

Once you get used to reading these pointers, it becomes rather trivial – byte swap the last two pointers to get the file ID (0 or 1 in all of the above rows), and byte swap the first four bytes to get the page ID. To make it a bit more easier for myself and for those who do not read HEX natively, I’ve made a simple function to convert the pointers into a more easily read format.

create function getPageLocationFromPointer
(
	@Pointer binary(6)
)
returns varchar(17)
begin

	return 
		'(' + 
		cast(
			cast(
				substring(@Pointer, 6, 1) +
				substring(@Pointer, 5, 1)
				as smallint
			) as varchar
		) +
		':' +
		cast(
			cast(
				substring(@Pointer, 4, 1) +
				substring(@Pointer, 3, 1) +
				substring(@Pointer, 2, 1) +
				substring(@Pointer, 1, 1)
				as int
			) as varchar
		) +
		')'

end

While not beautiful, it is rather simple. The result:

select
	first_page,
	dbo.getPageLocationFromPointer(first_page) as first_page_location,
	root_page,
	dbo.getPageLocationFromPointer(root_page) as root_page_location,
	first_iam_page,
	dbo.getPageLocationFromPointer(first_iam_page) as first_iam_page_location
from
	sys.system_internals_allocation_units
Mark S. Rasmussen
I'm the CTO at iPaper where I cuddle with databases, mold code and maintain the overall technical & team responsibility. I'm an avid speaker at user groups & conferences. I love life, motorcycles, photography and all things technical. Say hi on Twitter, write me an email or look me up on LinkedIn.