04-11-2013 03:43 AM
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
Solved! Go to Solution.
05-03-2013 08:14 AM
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
04-11-2013 05:38 AM
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.
04-11-2013 05:39 AM
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.
04-11-2013 08:41 AM
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
04-12-2013 01:17 AM
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
04-12-2013 02:24 AM
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
04-12-2013 03:28 AM
05-03-2013 04:05 AM
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 ?
05-03-2013 05:26 AM
05-03-2013 07:07 AM
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
05-03-2013 07:28 AM
you should do
[mukpbcc1sql005a\pc001].EVVSEVA0010_22.dbo.ArchivePoint AP
05-03-2013 07:42 AM
now i get this error - see attached
05-03-2013 08:14 AM
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