cancel
Showing results for 
Search instead for 
Did you mean: 

EV SQL index query

GTK
Level 6

Hi

 

i am looking for a SQL query that will spit out how much Index data has been added to a particular EV server over a 3 month period or whatever date period i specify. Is this possible?

 

thanks

 

1 ACCEPTED SOLUTION

Accepted Solutions

JesusWept3
Level 6
Partner Accredited Certified

I'm not sure where you're getting this from:
mukpbcc1eva0010_on_[mukpbcc1sql005a\pc001].EVVSEVA0010_22.dbo.ArchivePoint AP

it should just be
[mukpbcc1sql005a\pc001].EVVSEVA0010_22.dbo.ArchivePoint AP

 


Run this query as is
 

SELECT A.ArchiveName "Archive", 
       IV.IndexedItems "Items In Index",
       CE.ComputerName "EV Server",
       (IRP.IndexRootPath + '\' + IV.FolderName) "Index Location",
       COUNT(S.IdTransaction) "Items Archived",
       SUM(S.ItemSize) "Size of items (KB)",
       SUM(S.ItemSize)*0.13 "Estimated Index Size (KB)"
FROM   EnterpriseVaultDirectory.dbo.IndexVolume IV,
       EnterpriseVaultDirectory.dbo.IndexRootPathEntry IRP,
       EnterpriseVaultDirectory.dbo.IndexingServiceEntry ISE,
       EnterpriseVaultDirectory.dbo.ComputerEntry CE,
       EnterpriseVaultDirectory.dbo.Root R,
       EnterpriseVaultDirectory.dbo.Archive A,
       [mukpbcc1sql005a\pc001].EVVSEVA0010_22.dbo.ArchivePoint AP,
       [mukpbcc1sql005a\pc001].EVVSEVA0010_22.dbo.Saveset S
WHERE  S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND  AP.ArchivePointId = R.VaultEntryId
  AND  R.RootIdentity = A.RootIdentity
  AND  R.RootIdentity = IV.RootIdentity
  AND  IV.IndexRootPathEntryId = IRP.IndexRootPathEntryId
  AND  IRP.IndexServiceEntryId = ISE.ServiceEntryId
  AND  ISE.ComputerEntryId = CE.ComputerEntryId
  AND  S.ArchivedDate > DATEADD(MONTH, -3, GETDATE())
GROUP BY A.ArchiveName, IV.IndexedItems, CE.ComputerName, IRP.IndexRootPath, IV.FolderName
https://www.linkedin.com/in/alex-allen-turl-07370146

View solution in original post

12 REPLIES 12

GertjanA
Moderator
Moderator
Partner    VIP    Accredited Certified

Hello GTK,

I do not believe it is possible to asses size of index from SQL.

Only thing I can think of is to figureout how much has been archived, and then take the percentage given from the Admin guide for the indexlevel you use (ie 13% for full)

we use a diskmonitor report that runs once a week to keep some track.

Regards. Gertjan

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

No, the size of the indexes are not tracked in sql.   You could guesstimate by running a query for for original size of archived items and then taking 15% or that.

JesusWept3
Level 6
Partner Accredited Certified

If you did want to do the guesstimated way, the query would look like this

SELECT A.ArchiveName "Archive", 
       IV.IndexedItems "Items In Index",
       CE.ComputerName "EV Server",
       (IRP.IndexRootPath + '\' + IV.FolderName) "Index Location",
       COUNT(S.IdTransaction) "Items Archived",
       SUM(S.ItemSize) "Size of items (KB)",
       SUM(S.ItemSize)*0.13 "Estimated Index Size (KB)"
FROM   EnterpriseVaultDirectory.dbo.IndexVolume IV,
       EnterpriseVaultDirectory.dbo.IndexRootPathEntry IRP,
       EnterpriseVaultDirectory.dbo.IndexingServiceEntry ISE,
       EnterpriseVaultDirectory.dbo.ComputerEntry CE,
       EnterpriseVaultDirectory.dbo.Root R,
       EnterpriseVaultDirectory.dbo.Archive A,
       yourVaultStore.dbo.ArchivePoint AP,
       yourVaultStore.dbo.Saveset S
WHERE  S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND  AP.ArchivePointId = R.VaultEntryId
  AND  R.RootIdentity = A.RootIdentity
  AND  R.RootIdentity = IV.RootIdentity
  AND  IV.IndexRootPathEntryId = IRP.IndexRootPathEntryId
  AND  IRP.IndexServiceEntryId = ISE.ServiceEntryId
  AND  ISE.ComputerEntryId = CE.ComputerEntryId
  AND  S.ArchivedDate > DATEADD(MONTH, -3, GETDATE())
GROUP BY A.ArchiveName, IV.IndexedItems, CE.ComputerName, IRP.IndexRootPath, IV.FolderName

Replace "yourVaultStore" with a name of the vault store you wish to use

https://www.linkedin.com/in/alex-allen-turl-07370146

GTK
Level 6

hi JesusWept3

 

i have 2 questions:

1 - is the colume - 'Estimated Index size' - the total size of the index OR the size th eindex has grown in the last 3 months (or whatever date specified) ?

2 - the query only runs against the EV directory DB however our journal DBs are on a separate SQL instance. How can i get the figures required from our Journal DBs ?

 

thanks

GertjanA
Moderator
Moderator
Partner    VIP    Accredited Certified

Hello GTK,

1 - don't know.

2 - you need to setup 'linked server' in SQL.

we have same. EV01 = directory and journal databases, and EV02 = mail databases.

Queries needing both directory and maildatabases look like: (runs on EV01)

FROM   EnterpriseVaultDirectory.dbo.Archive A, EnterpriseVaultDirectory.dbo.Root R,
ev02_on_SQLSERVERNAME.EVVSDatabase_2.dbo.ArchivePoint AP
 

Regards. Gertjan

JesusWept3
Level 6
Partner Accredited Certified
The total item size and the guesstimated index are for whatever period of time you put in he WHERE a.archivedate > dateadd() etc The only one that would be confusing is the total items in index which is how many the items are in the index regardless of when they were archived And Gertjan is spot on, you will have to add a linked server
https://www.linkedin.com/in/alex-allen-turl-07370146

GTK
Level 6

I am getting error Could not find server 'evserver_on_sqlserver' in sys.servers

 

where is sys.servers located so i can check the values in here ?

JesusWept3
Level 6
Partner Accredited Certified
Any chance of a screenshot?
https://www.linkedin.com/in/alex-allen-turl-07370146

GTK
Level 6

attached screenshot

 

mukpbcc1eva0010 is the EV server

 

mukpbcc1sql0008 is the physical node in the sql cluster

 

if i try entering the SQL instance - mukpbcc1sql005a\pc001 - SQL does not like \ in the code

JesusWept3
Level 6
Partner Accredited Certified

you should do

[mukpbcc1sql005a\pc001].EVVSEVA0010_22.dbo.ArchivePoint AP

https://www.linkedin.com/in/alex-allen-turl-07370146

GTK
Level 6

now i get this error - see attached

JesusWept3
Level 6
Partner Accredited Certified

I'm not sure where you're getting this from:
mukpbcc1eva0010_on_[mukpbcc1sql005a\pc001].EVVSEVA0010_22.dbo.ArchivePoint AP

it should just be
[mukpbcc1sql005a\pc001].EVVSEVA0010_22.dbo.ArchivePoint AP

 


Run this query as is
 

SELECT A.ArchiveName "Archive", 
       IV.IndexedItems "Items In Index",
       CE.ComputerName "EV Server",
       (IRP.IndexRootPath + '\' + IV.FolderName) "Index Location",
       COUNT(S.IdTransaction) "Items Archived",
       SUM(S.ItemSize) "Size of items (KB)",
       SUM(S.ItemSize)*0.13 "Estimated Index Size (KB)"
FROM   EnterpriseVaultDirectory.dbo.IndexVolume IV,
       EnterpriseVaultDirectory.dbo.IndexRootPathEntry IRP,
       EnterpriseVaultDirectory.dbo.IndexingServiceEntry ISE,
       EnterpriseVaultDirectory.dbo.ComputerEntry CE,
       EnterpriseVaultDirectory.dbo.Root R,
       EnterpriseVaultDirectory.dbo.Archive A,
       [mukpbcc1sql005a\pc001].EVVSEVA0010_22.dbo.ArchivePoint AP,
       [mukpbcc1sql005a\pc001].EVVSEVA0010_22.dbo.Saveset S
WHERE  S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND  AP.ArchivePointId = R.VaultEntryId
  AND  R.RootIdentity = A.RootIdentity
  AND  R.RootIdentity = IV.RootIdentity
  AND  IV.IndexRootPathEntryId = IRP.IndexRootPathEntryId
  AND  IRP.IndexServiceEntryId = ISE.ServiceEntryId
  AND  ISE.ComputerEntryId = CE.ComputerEntryId
  AND  S.ArchivedDate > DATEADD(MONTH, -3, GETDATE())
GROUP BY A.ArchiveName, IV.IndexedItems, CE.ComputerName, IRP.IndexRootPath, IV.FolderName
https://www.linkedin.com/in/alex-allen-turl-07370146