# of items in Index Database

[EVStrategist]

Is there a sql query to determine the 'total' number of items in an index. So as to determine if the index has reached the recommended 100 million items limit.

I have one EV site with indexing that has 8 locations on each server - most servers have over 1.3 , some over 2 tb of index volume space (D: split up in to 8 directories).  I want to see if some directories(each of the 8 index volumes) have space available for more indexing to be written to them.  If so I have two options:  1) continue to add more space to the D: increasing the total size even more over 2TB  or 2) create a new LUN and move one of the index locations to that LUN (Say F: or G:).  We currently add 300gb each time the space nears 90% full in order to not crash EV.   Over time the required free space (the 10% that we maintain) keeps growing as 10% of an ever increasing LUN (300, 600 then 900, 1.2tb, 1.5 tb and now 1.8+TB) grows from initially requiring only 30gb free, to 60Gb free, to 90Gb free to now 180Gb free to keep under 90% used.  This is a waste of space.  We have several LUNS that have 200-300 Gb free on them but because they are at or over 90% used we need to add more space. My thoughts were to either 1) close all 8 locations off on D:  and create a G: and add 8 new locations (LUN of initially 300Gb) and let it grow over time. or 2) Move a single Location (say maybe 250-300Gb-one of the current 8 locations) to G: .  This would free up 250+GB on D: and I would have one index location on G:  with free space to grow.  However if any of these 8 locations are nearing 100million items in them, then I wont move I will just close them off and create a G: and add a new index location as required to maintain 8 open index locations.  

[TonySterling]

The 100 million limit is for the Vault Store database, not the EV indexes.  The EV indexes will roll over automatically.

You can run SELECT Count(*) FROM Saveset against your vault store database.

[TonySterling]

Just re-read your post a bit closer.  What version of EV are you?  If you are version 9 when you close an index location the indexes will continue to be written to until they roll-over. 

Let us know what version you are, it will help giving you the best advice.

[AndrewB]

small sidebar - as you say, 90% is relative and the larger the volume, the larger the amount of "wasted" space. if you're talking about the 90% default warning threshold in EV, while might make sense for smaller volumes, it is something you can customize to suit your environment.  

(you gave an explanation in your post that i'm not sure adds up. just wanted to point out that 90% of one 2TB LUN is the same as 90% of two 1TB LUNs)

[EVStrategist]

each server has a single LUN(D:) (some up to 2.3 TB) with 8 Index volumes(locations) on each. EV 9.0.2.

90% is a relative value and is a figure we set ourselves to monitor and alert on as to when to add more space to the LUN.  Upon Rollover(Whenever that happens at the 100 million mark) it will auto rollover to an open index location - currently we have no empty open index locations-all 8 are in use.   I sort of want to cap the current LUN (D:) at the 2TB mark anyways as anything over that size to me is getting too large and potential of other issues.  I would like to either move some of the volumes over to a G: LUN .  By determining if any location is nearing 100 million, I can better plan for what path I should take.

[JesusWept3 · Accepted solution]

This should give you a good starting point

SELECT A.ArchiveName, (IRP.IndexRootPath + '\'+ IV.FolderName) AS Folder, IV.FirstItemSequenceNumber AS FirstISN, IV.HighestItemSequenceNumber AS LastISN, IV.IndexedItems, IV.Rebuilding, IV.Failed, IV.FailedItems
FROM IndexRootPathEntry IRP,
       IndexVolume IV,
       Archive A,
       Root R
WHERE A.RootIdentity = R.Rootidentity
  AND R.RootIdentity = IV.RootIdentity
  AND IV.IndexRootPathEntryId = IRP.IndexRootPathEntryId
  AND A.ArchiveName = 'your Archive Name'

[TonySterling]

In EV 9 Indexes will roll over automatically when the AVS Max Location reaches 1 Billion unless you have lowered the default setting by overriding it with a registry key.  So can you clarify what you are referring to when you are talking about the 100 million mark?  If you can tell us where you got that number from it will help.  If you got it from the Performance guide, again, it is talking about the Vault Store database in SQL, not the EV indexes.

One thing you should consider is EV 10.  In EV 10 when you close an Index location no new items are written to it.  Are you planning to upgrade?  If yes, once you upgrade you could close all your existing locations and open new ones.  As part of the upgrade, the existing 32 bit indexes are no longer written to and new 64 indexes will be created.  It sounds like it would help you control your indexes volumes a lot better.

[EVStrategist]

now I'm not a sql guru...can you assist in terms of what value need to be modified to match my instance...

Let me add a bit more clarification: 

our EV Archiving servers have a single Index Location used by all archives, not 8 index locations.

our Journal servers are configured correcly to 8 separate Index locations(still all on D:), and each archive is configured to use all 8 locations. So there are sub folders called D:\Index\Index1, D:\Index\Index2  so on and each archive will have sub folders created for their archive under each location

e.g.  Root Index location d:\Index      or coudl be D:\Index\Index1 through D:\Index\Index8

       DB name ...e.g.  EVMBXStoreYOW01

       archive GUID - 1730AB39474GF...........EVNA

[EVStrategist]

no plans to Upgrade to EV 10 until early next year.

[AndrewB]

where does EV keep track of that 1 billion item mark for rollover? maybe that can be queried to help get what he's looking for.

[JesusWept3]

EV doesn't keep track of it, it is altavista that keeps a count of the number of locations.
But it really doesn't matter to be honest.

The best thing you can do is move the bigger indexes to another set of index volumes, close the others so that smaller ones will take its space, and so on and so forth.

 

[TonySterling]

It is tracked in a file in the individual Index volume root called dataset.  I don't believe there is an easy way to parse all those files as I don't think IndexCheck reads that specific information.

[JesusWept3]

The SQL query will just give you a list of archives and their index locations and the amount of items indexed within each one, its purely for read only purposes.

So what i would do is just take the biggest ones and move them to other locations, close both locations and open index locations 3 through to 8 etc and let them start growing there, either from new indexes or a rollover


So for instance if you have

E:\EV Indexes\Index1\
and that has 5000 archives totalling 2TB, i personally would make an \index2\ location on another drive, and get at least 1TB of the data over there, and then close both \index1\ and \index2\

After that i would open \index3\ \index4\ \index5\ etc and make them all open.
Your \index1\ and \index2\ will still continue to grow until the indexes roll over, or all the archives have become deleted, or all the users no longer use EV etc.
The chances of an index rolling over though on a user index are very very very small.
 

[EVStrategist]

so my plan would be to move at least two of the index locations(for Journaling) ,will be probably 500GB or so, to a new LUN, leave all 8 locations still open....if one does happen to rollover, there will be ample space regardless.  So I would have 6 locations on the D:\Index location labelled  D:\Index\Index1  through D:\Index\Index6 and then on the G: Lun have a the other two index location-G:\Index\Index7 and G:\Index\Index8.  This would reduce space requirement on the D: and allow G: to grow slowly.

as for the EV Archive servers that have a single location.  I would need to create a G:\Index and simply matchup certain archives and move their associated indexes over to the G:\Index under the single folder.

or  as you suggested, move 2 indexes to G:, close them both, create at least one new open index location for anticipated rollover.....

This config can stay long enough to allow us to migrate to EV 10 and also prevent wasting of so much space on the D:....with the freed up space of moving the indexes to G: D: would have a very large amount of free space....and agreed, the individual archives probably will never roll over but the Journal archives do have a potential of rolling over.

[EVStrategist]

Msg 208, Level 16, State 1, Line 1

Invalid object name 'IndexRootPathEntry'.

error when copy and pasted to SQL

[AndrewB]

which database are you running it against? i think it needs to be EnterpriseVaultDirectory

[JesusWept3]

yeah its probably trying to run it against the master DB

[EVStrategist]

I executed it on the Directory database:

results:

EV Archving site- sql query comes up with all 26000+ archives. Total # of items 1.2 trillion(1,183,212,053). 

I need some way of breaking it down by archiving server's D:.  Each server (10 of them) has between 2500 and 3000 user archives on each.  The SQL output doesn't detail the home archiving server.

I exported the archive listing and matched up the archives location with the SQL output listing the archive and # if items in the indexes created:

result for EV User Archiving: listing # if items on the D: - each server has a single index location configured...so e.g. Serve 1 has 192 million items in the index for that single location , total size of the D: is 1 Tb with 200 Gb free. Should I close this index location and let any new inex info be created on a G: in order to not have to increase the LUN size for D:>...at what point do I say the D: is large enough and create a new index location, regardless of the # of items in the index.   192 million is over the 100 million that a Symantec Engineer informed me it should be maxxed out at. No new index location is configured so not possible to rollover I guess.  

Server1	192,680,471
Server2	96,829,073
Server3	103,388,570
Server4	128,262,749
Server5	9,075,139
Server6	541,703
Server7	95,164,971
Server8	36,397,051
Server9	31,369,538
Server10	61,902,397

Journal sites-which have 8 index locations per server and archive's indexes spread between all 8 index locatons show that each archive has approx. 30 million items indexed. So per archive is an acceptable amount, but there is no way to determine how those 30 million items are split between the 8 index locations.  I would have to do this for each archive and add up the total from all the calcs. ANy ideas about this one.

[AndrewB]

first of all, that's 1.183 Billion, not 1.2 trillion! second, if a symantec engineer told you about a 100 million item limit it's vault store db like Tony said, not index location so you're communications in this thread are mixed up.

[EVStrategist]

this will be the typical Journal Indexing locaitons summary:  Index locaiton on D: and # of indexed items.  Each location seems well under any limit. 

1		12,499,874
2		17,848,745
3	57	16,374,133
4	71	22,275,005
5	60	16,471,875
6	60	16,770,238
7	73	22,963,170
8	70	20,567,516

So I would just move a couple index locations to another LUN (G:) as a solution to not keep growing the exisitng D: :Lun beyond say 1.5tb or so.  Tha towuld resolve the issue for the Journal servers.

My apologies mentioning the 100million items for Indexing,  The total # oif items really want' the prime issue, it was finding a way to prevent growing the existing index location beyond huge sizes.  Not sure then if there is an upper recommended limit on the # of items in a Journal index  then. 

And I'm ok with the max 100 million items in a user archive Index as there are none even close.  So it's just moving around some indexes to arrange space approriately there as well.