03-19-2012 09:03 AM
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.
Solved! Go to Solution.
03-19-2012 10:08 AM
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'
03-19-2012 09:18 AM
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.
03-19-2012 09:21 AM
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.
03-19-2012 09:30 AM
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)
03-19-2012 09:47 AM
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.
03-19-2012 10:08 AM
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'
03-19-2012 10:25 AM
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.
03-19-2012 10:38 AM
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
03-19-2012 10:39 AM
no plans to Upgrade to EV 10 until early next year.
03-19-2012 11:16 AM
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.
03-19-2012 11:27 AM
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.
03-19-2012 11:31 AM
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.
03-19-2012 11:31 AM
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.
03-19-2012 11:45 AM
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.
03-19-2012 12:08 PM
Msg 208, Level 16, State 1, Line 1
Invalid object name 'IndexRootPathEntry'.
error when copy and pasted to SQL
03-19-2012 12:12 PM
which database are you running it against? i think it needs to be EnterpriseVaultDirectory
03-19-2012 12:13 PM
yeah its probably trying to run it against the master DB
03-19-2012 01:55 PM
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.
03-19-2012 02:06 PM
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.
03-19-2012 02:41 PM
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.