Index rebuild date/time

Question

Index rebuild date/time

Yuri55

SSCrazy Eights

Points: 8449
More actions
January 28, 2007 at 8:56 am

#194027

For some unknown reason today “ReIndex_All” job was running almost 8 hours (instead of normal 20 minutes)-eventually I canceled it. I would like to check where (tables) indexes were rebuilt and where- not. Could anybody remind me please view (it’s Sql Server 2005) or sys table where I can check index last rebuild date/time? Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

MohammedU SSCoach Points: 19644 More actions · Answer 1

I don't think there is any column which gives this info...

But if auto update stats is not enabled on your db then you can use the following script to get the stats date which is nothing but the date of reindex...

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

MohammedU
Microsoft SQL Server MVP

Yuri55 SSCrazy Eights Points: 8449 More actions · Answer 2

Thanks for your help- AutoUpdate Statistics is True. I remember I saw here (Sql Central Forum) answer- returned value something like "modifydate" for each table index. And I used it to check where (table) indexes were rebuilt! Unfortunately (shame on me) I forgot what was that- view, sysobject, etc. Anyhow thanks again

Vivien Xing SSChampion Points: 12297 More actions · Answer 3

There is a column modify_date in sys.objects table which means:

Date the object was last modified by using an ALTER statement. If the object is a table or a view, modify_date also changes when a clustered index on the table or view is created or altered. -- BOL "sys.objects (Transact-SQL)"

-- It seems work for nonclustered index as well.

-- example:

Declare

@dbid int

Select

@dbid = db_id('mydb')

Select

objectname=object_name(i.object_id)

, indexname=i.name, i.index_id

, o.create_date, o.modify_date

from

sys.indexes i, sys.objects o

where

objectproperty(o.object_id,'IsUserTable') = 1

and

i.index_id NOT IN

(select s.index_id

from sys.dm_db_index_usage_stats s

where s.object_id=i.object_id and

i

.index_id=s.index_id and

database_id

= @dbid )

and

o.object_id = i.object_id

order

by objectname,i.index_id,indexname asc

Yuri55 SSCrazy Eights Points: 8449 More actions · Answer 4

Thanks for your help. It's true but there are couple "buts": 1) when table itself was modifyed this value changed also and 2) when table has some indexes rebuilding any (not clustered only- I just checked) one will change this data, i.e. you can not be sure what index was rebuild. Anyhow thanks one more time- at least I have something now.

ankur.gupta29 SSC Journeyman Points: 78 More actions · Answer 5

This code does not give the correct info as Modify and create date is corresponding to the object(tables etc) thereby if you modify the name of the table or a single index in the table you will get an incorrect information as it update(Column --Modify_Date) all the rows for that object.

MudLuck SSCrazy Points: 2301 More actions · Answer 6

Everyone else looks at stats table I knew there was a datetime in the indexes system table... Why do I even go out on google anymore. Thanks SSC 😀

coolchaitu SSChampion Points: 10334 More actions · Answer 7

Could you please share the query to check last time when an index was rebuilt