How to get the last index rebuild date?

• gehan.nanayakkara

  Old Hand

  Points: 337

  More actions

  May 25, 2011 at 7:42 pm

  #240983

  I've checked these but they aren't that helpful...

  SELECT * FROM sys.dm_db_index_usage_stats (I mean information from these tables)

  As far as I'm aware when ever indexes are rebuilt the statistics get updated,

  E.g.

  SELECT 'Index Name' = i.name,

  'Statistics Date' = STATS_DATE(i.object_id, i.index_id)

  FROM sys.objects o

  JOIN sys.indexes i ON o.name = 'Address' AND o.object_id = i.object_id;

  GO

  But I think this is not accurate enough...Because I just want to create a report that I can run to get this information from sql server boxes....

  Any help appreciated.....

• Ryan007

  SSCrazy Eights

  Points: 8275

  More actions

  May 26, 2011 at 3:10 am

  #1330117

  Select Name as IndexName,

  STATS_DATE ( object_id , index_id ) as IndexCreatedDate

  From sys.indexes

  Ryan
  //All our dreams can come true, if we have the courage to pursue them//

• Latheesh NK

  SSCertifiable

  Points: 6559

  More actions

  May 26, 2011 at 4:03 am

  #1330123

  There is no strict way to find the last rebuil date time info...

  Please refer the below links:

  http://sqlblog.com/forums/thread/13120.aspx

  http://www.sqlservercentral.com/Forums/Topic340343-149-1.aspx#bm340730

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  May 26, 2011 at 4:27 am

  #1330136

  Sumanta Roy (5/26/2011)

  ---

  Select Name as IndexName,

  STATS_DATE ( object_id , index_id ) as IndexCreatedDate

  From sys.indexes

  That is not the last rebuild date of the index, it's the date the statistics were last updated. Since they can auto-update, that could be anything.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Ryan007

  SSCrazy Eights

  Points: 8275

  More actions

  May 26, 2011 at 4:46 am

  #1330155

  thanks Gail.

  Ryan
  //All our dreams can come true, if we have the courage to pursue them//