checking inventory of indexes

on a big database, and if 3-4 DBAs have worked on it on and off, how do you best track of all indexes deployed so far. are DBAs to document which index was created for what purpose, when etc so others can infer later why an index exists? (instead of going to DMV to check if an index is used or not.. some indexes may be seasonal.. only run on month ends or quarterly etc).

any other thoughts to make this efficient?
LVL 5
25112Asked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
>> how can you do backup of extended properties? <<

I use view sys.extended_properties, backing it up, but be sure to first decode the ids -- major_id, minor_id -- to names in case the id changes.  That may not be an "official" answer, but it works for me :-).
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
SELECT * FROM sys.indexes, but this doesn't allow a DBA to store a description value that would help document the index, so they would have had to leave some other documentation such as email, Excel, Word on the context of why the index exists.
0
 
Scott PletcherSenior DBACommented:
I use a table for such documentation.

If you don't want to do that, add extended properties to contain that type of info.  But be sure to do a separate back up of those properties in case the index is dropped.
0
 
25112Author Commented:
good ideas. thanks.


>> separate back up of those properties
how can you do backup of extended properties?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.