Find the date and time that a data page was modified

Question

Is there a method (documented or undocumented) to determine the last time a data page was changed in a SQL Server 2012 database data file.

Answer 1

If you know the file and page number already:

DECLARE @file INT, @page INT; -- input parameters


SET @file = 1;    -- default, most systems
SET @page = 1681; -- exemplar on my system; you'll need to change


DECLARE @p NVARCHAR(15) = RIGHT(N'000' + CONVERT(NVARCHAR(4), @file), 4) 
  + N':' + RIGHT(CONVERT(NVARCHAR(10),CONVERT(VARBINARY(32),@page),1),8);

;WITH x AS 
(
  SELECT [Page ID],[Transaction ID] 
    FROM ::fn_dblog(NULL,NULL) 
    WHERE [Page ID] = @p
)
SELECT MAX([End Time]) FROM ::fn_dblog(NULL,NULL) AS y
INNER JOIN x ON x.[Transaction ID] = y.[Transaction ID]
WHERE Operation = N'LOP_COMMIT_XACT';

If you don't know the page already, you may need to do some up-front investigation using DBCC IND, DBCC PAGE, and various catalog views, especially if you have partitioning enabled, multiple files in the target filegroup, etc.