I wrote a blog before“ Can the last index rebuild time be accurately obtained in SQL Server? ", which mainly deals with three questions: can we find the creation time of the index? When was the last index rebuild? When was the last index reorganize? At that time, we came to the conclusion that the answer was that we could not accurately find the time of index creation, index reorganization and index reconstruction. But I saw a blog recently.“ SQL Server – Get Index Creation Date ”, and then studied, even if SQL Server does not have a system table or DMV view for the time being to save index creation, index reconstruction time and index reorganization time. But we can get their values through the system trace file. Of course, there are restrictions. Not all indexes can find these values. Please refer to the following detailed explanation:

 

 

 

Index creation time

 

The creation time of the index can be obtained with the following SQL, but we know that the trace may be stopped or disabled; the trace file may also be overwritten. So this method can only query the latest period of time. It has a strong timeliness. So this method is not universal. It is doomed that it can only be used as a method reference, but not universal.

 

 

DECLARE @filename VARCHAR(500) 

SELECT @filename = CAST(value AS VARCHAR(500)) 

FROM fn_trace_getinfo(DEFAULT) 

WHERE property = 2 

  AND value IS NOT NULL 

-- Go back 4 files since default trace only keeps the last 5 and start from there.

SELECT @filename = substring(@filename, 0, charindex('_', @filename)+1) + convert(varchar, (convert(int, substring(left(@filename, len(@filename)-4), charindex('_', @filename)+1, len(@filename)))-4)) + '.trc'

SELECT 

       gt.EventClass, 

       gt.EventSubClass,

       te.Name AS EventName,

       gt.HostName, 

       gt.StartTime, 

       gt.DatabaseName,

       gt.ObjectName,

       gt.IndexID

FROM fn_trace_gettable(@fileName, DEFAULT) gt 

JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id 

WHERE EventClass = 46

  and ObjectType = 22601

  and gt.DatabaseName <> 'tempdb'

ORDER BY StartTime desc; 

 

 

 

Index reconstruction time & Index reorganization time

 

 

As shown below, the trace event ID of object: changed is 164. We can't distinguish alter index... Rebuild from alter index... Reorganize. For index reconstruction and index reorganization, the TextData returned by FN trace gettable is Null and can't be judged. So we can record the exact time here, but we can't distinguish between index reconstruction and index reorganization.

 

 

 

 

DECLARE @filename VARCHAR(500) 

SELECT @filename = CAST(value AS VARCHAR(500)) 

FROM fn_trace_getinfo(DEFAULT) 

WHERE property = 2 

  AND value IS NOT NULL 

-- Go back 4 files since default trace only keeps the last 5 and start from there.

SELECT @filename = substring(@filename, 0, charindex('_', @filename)+1) + convert(varchar, (convert(int, substring(left(@filename, len(@filename)-4), charindex('_', @filename)+1, len(@filename)))-4)) + '.trc'

SELECT 

       gt.EventClass, 

       gt.EventSubClass,

       te.Name AS EventName,

       gt.HostName, 

       gt.StartTime, 

       gt.DatabaseName,

       gt.ObjectName,

       gt.IndexID

FROM fn_trace_gettable(@fileName, DEFAULT) gt 

JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id 

WHERE EventClass = 164

  and ObjectType = 22601

  and gt.DatabaseName <> 'tempdb'

ORDER BY StartTime desc; 

 

 

The test verification is as follows:

 

USE YourSQLDba;

GO

ALTER INDEX Pk_HistMaintTrav ON [Maint].[JobHistory] REBUILD;

ALTER INDEX PK_DataBaseSizeDtl_Day ON [Maint].[DataBaseSizeDtl_Day] REORGANIZE;

CREATE INDEX IX_DataBaseSizeDtl_Day_N1 ON [Maint].[DataBaseSizeDtl_Day](DataBaseName);

 

 

 

 

Note: the above script may make errors in some environments, mainly because of the path of trac file, for example, C: \ program files \ Microsoft SQL Server \ mssql10 \ u 50. MSSqlServer \ MSSQL \ log \ log \ u 603.trc will encounter the following errors, and the script needs to be modified according to the actual situation.

 

Msg 245, Level 16, State 1, Line 8

Conversion failed when converting the varchar value '50.MSSQLSERVER\MSSQL\Log\log_603' to data type int.

 

 

 

reference material:

 

https://sqlconjuror.com/sql-server-get-index-creation-date/