How to check when was the last Time re-indexing was performed on a DB

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » How to check when was the last Time...

How to check when was the last Time re-indexing was performed on a DB

Rate Topic

Display Mode

Topic Options

Author

Message

sqlpanther

Posted Monday, December 10, 2012 12:52 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:29 AM
Points: 26, Visits: 130

hello All,
Can someone suggest me a scrpit or a command that can provide me information about last time re-indexing performed on the DB, I know I can run DBCC CheckDB but its taking a lot of time, is there any other way?

Post #1394472

GilaMonster

Posted Monday, December 10, 2012 1:32 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:15 PM
Points: 37,651, Visits: 29,903

CheckDB won't tell you the last time an index was rebuilt (however you need to run it regularly).

That information isn't stored by default. If you're running a reindexing job, you can look at the job history and see when it last ran. Many custom index rebuild scripts have their own logging table.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1394478

Ramkumar (LivingForSQLServer)

Posted Tuesday, January 08, 2013 4:31 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 08, 2013 8:24 AM
Points: 153, Visits: 563

looks like modify_date in sys.objects updates rebuild date&time. but no further details available in system view on what caused that modifiation.
In short: answer to your question is no.

create database test_reindex
go
use test_reindex
go
create table reindex_test(col1 int primary key, col2 int)
go
create index nci_col2 on reindex_test(col2)
go

select * from sys.indexes where OBJECT_NAME(object_id) = 'reindex_test'
select create_date, modify_date from sys.objects where name = 'reindex_test'

alter index PK__reindex___357D0D3E7F60ED59 on reindex_test rebuild

select create_date, modify_date from sys.objects where name = 'reindex_test'

alter index nci_col2 on reindex_test rebuild

select create_date, modify_date from sys.objects where name = 'reindex_test'

Post #1404142

« Prev Topic | Next Topic »

Permissions