Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to check when was the last Time re-indexing was performed on a DB Expand / Collapse
Author
Message
Posted Monday, December 10, 2012 12:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 18, 2013 4:14 AM
Points: 38, Visits: 198
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
Posted Monday, December 10, 2012 1:32 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:15 AM
Points: 39,975, Visits: 36,334
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
Posted Tuesday, January 8, 2013 4:31 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 11, 2014 6:27 AM
Points: 153, Visits: 609
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 »

Add to briefcase

Permissions Expand / Collapse