07-16-2015 07:48 AM
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
07-16-2015 08:04 AM
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
07-17-2015 01:26 AM
hi Andrew
we are not permitted linked DBs in our environment
is there any other solution available?
07-17-2015 09:04 AM
could you restore a backup copy of the databases to a test server in your lab?
07-21-2015 02:07 PM
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
10-05-2015 05:43 AM
No need to run any query on SQL server, you can check on EV reporting