Tsql, know when index rebuild, reorg or updatestatistics was last run on SQL server

Question

Hi

Using Tsql, how can i know when Index rebuild, reorg or updatestatistics was last run on SQL server (2000, 2005 or 2008)?

Regards

Answer 1

SQL Server does not store this information. You can get an "approximate" estimate based on the date the last time the statistics were updated for a given Index as a REBUILD operation will also update the statistics for the Index.

Here is an example using the AdventureWorks database:

USE AdventureWorks;
GO

SELECT name AS Stats,
STATS_DATE(object_id, stats_id) AS LastStatsUpdate
FROM sys.stats
WHERE object_id = OBJECT_ID('Sales.SalesOrderDetail')
and left(name,4)!='_WA_';

GO

You may want to consider using one of the many freely available Index Tuning maintenance scripts for SQL Server. These provide a means to store the details of the Index operations that are performed on a given database and thereby provide information as to when an Index was last rebuilt.

Take a look at the excellent Index Maintenance Script written by Michelle Ufford