Post reply

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

  • December 10, 2012 at 12:52 am

    #285982

    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?

  • December 10, 2012 at 1:32 am

    #1566924

    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, 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

  • January 8, 2013 at 4:31 am

    #1574738

    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'

Viewing 3 posts - 1 through 2 (of 2 total)

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