Tsql, Know When Index Rebuild, Reorg Or Updatestatistics Was Last Run On Sql Server

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

Solution 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

Solution 2:

For SQL Server 2000, the following script may be needed as sys.stats does not exist in SQL 2000:

DBCC SHOW_STATISTICS(TABLENAME, INDEX_NAME)

Many thanks for your script John!

EDIT: I've been contemplating this script for SQL 2005+ (and have had a need to see the data in TableName, IndexName, LastStatsUpdate format), and I came up with the following script. I'm sure it needs polishing, so feel free to tweak as necessary.

SELECT OBJECT_NAME(object_id) [TableName], 
       name [IndexName],
       STATS_DATE(object_id, stats_id) [LastStatsUpdate]
FROM sys.stats
WHERE name NOTLIKE'_WA%'AND STATS_DATE(object_id, stats_id) ISNOT NULL
AND OBJECTPROPERTY(object_id, 'IsMSShipped') = 0ORDERBY TableName, IndexName