2

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.

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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