DBA Quiz – an answer

I had announced a question at  DBA Quiz held at beyondrelational.com in mid-September, so now it’s about time to provide an answer expected.

First of all, there were two parts of it – a location of data on a disk and a method of obtaining it. The answer has two parts – first find page ID, then find the data inside the page.

In order to get a page ID for data you can use undocumented functions %%physloc%% and sys.fn_PhysLocFormatter:

SELECT sys.fn_PhysLocFormatter (%%physloc%%) AS [Physical RID], * 
FROM TableLocateTest.dbo.t2

Returned Physical RID format is FileID:PageID:Offset – FileID may be used to join to sys.files or sys.sysfiles to get database file name.
Note – if it was SQL Server 2005, DBCC IND could be used.

Then you can use another undocumented DBCC command – DBCC PAGE – to inspect contents of the page identified by ID found above. Remember to enable trace flag 3604 (send trace output to the client) before issuing DBCC PAGE.

DBCC PAGE('TableLocateTest', FileID, PageID, [2 | 3]) 

And that’s it – using those two commands you can retrieve physical location of data on disk.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s