Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

EV SQL index query

Created: 11 Apr 2013 • Updated: 07 Jun 2013 | 12 comments
GTK's picture
This issue has been solved. See solution.

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

 

Operating Systems:

Comments 12 CommentsJump to latest comment

GertjanA's picture

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.

Thank you, Gertjan, MCSE, MCITP,MCTS, SCS, STS
Company: www.t2.nl

www.quadrotech-it.com

www.symantec.com/vision

TonySterling's picture

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's picture

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

GTK's picture

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's picture

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
 

Thank you, Gertjan, MCSE, MCITP,MCTS, SCS, STS
Company: www.t2.nl

www.quadrotech-it.com

www.symantec.com/vision

JesusWept3's picture

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

GTK's picture

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's picture

Any chance of a screenshot?

GTK's picture

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

Capture.JPG
JesusWept3's picture

you should do

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

GTK's picture

now i get this error - see attached

Capture2.JPG
JesusWept3's picture

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
SOLUTION