cancel
Showing results for 
Search instead for 
Did you mean: 

EV SQL query

GTK
Level 6

hi

 

We are using EV 8 SP5.

i need a SQL query that gives the total size of an archive. The VS DBs are on a seperate SQL instance to the directory DB so i will need a JOIN in the query somehwere.

 

Can anyone help?

 

Thanks

 

5 REPLIES 5

AndrewB
Moderator
Moderator
Partner    VIP    Accredited

a sql join is for something else...  you have to create a linked server and here's an article about how to do that: http://stackoverflow.com/questions/1144051/selecting-data-from-two-different-servers-in-sql-server

this is your standard archive size script:

SELECT A.ArchiveName, 
       COUNT(S.ItemSize) "Item Count",
       SUM(S.ItemSize)/1024 "Total Size (MB)"
FROM   <VaultStoreDB>.dbo.Saveset S,
       <VaultStoreDB>.dbo.ArchivePoint AP,
       EnterpriseVaultDirectory.dbo.Root R,
       EnterpriseVaultDirectory.dbo.Archive A
WHERE  S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND  AP.ArchivePointId = R.VaultEntryId
  AND  R.RootIdentity = A.RootIdentity
GROUP BY A.ArchiveName
ORDER BY A.ArchiveName

GTK
Level 6

hi Andrew

 

we are not permitted linked DBs in our environment

 

is there any other solution available?

AndrewB
Moderator
Moderator
Partner    VIP    Accredited

could you restore a backup copy of the databases to a test server in your lab?

Chris_Warren
Level 5
Employee Accredited Certified

If you can't link DB's for security purposes, or backup and redirect restore the directory to the same instance of the Vault Store DB, you can still get most of the information, just not the Archive Name.

Use VaultStoreDBName
SELECT AP.ArchivePointId, 
       COUNT(S.ItemSize) "Item Count",
       SUM(S.ItemSize)/1024 "Total Size (MB)"
FROM   Saveset S,
       ArchivePoint AP
WHERE  S.ArchivePointIdentity = AP.ArchivePointIdentity
GROUP BY AP.ArchivePointId
ORDER BY AP.ArchivePointId

 

The ArchivePointID is the ArchiveID of the individual Archive. In the VAC, if you hold Shift+Ctrl and right-click on Archives, you have Find Archive or Folder which you can look this ArchiveID up for the name.

Alternatively, you can then run a subsequent query against the EnterpriseVaultDirectory to translate a list of the ArchivePointID's to the Archive Names.

Use EnterpriseVaultDirectory
Select ArchiveName, VaultEntryId as "ArchiveID"
From ArchiveView
WHERE VaultEntryId IN (
'18D287ABC023A404DB65207478B7F95021110000evserver1',
'183BC71D49454BC4398148B7C131AE7791110000evserver1',
)

Then use Excel to relate to two results.

I hope this helps!

Chris

sandeepk
Level 5
Partner Accredited

No need to run any query on SQL server, you can check on EV reporting