SQL Query to find total archived files and size for each Target FSA File server

Created: 14 Nov 2012 | Updated: 15 Nov 2012

Can anyone pelase help with a SQL query

I need a SQL Query to find total archived files and size for each Target FSA File server please.

Not per archive basis, per file server and for all archive points within each file server.

Many Thanks in advance

Quick Look Solution

I think this query should

I *think* this query should work, its not tested but it works in theory, change "YourVaultStore" to the name of your vault store db that holds FSA Archives

SELECT FSE.DnsName "File Server",
       COUNT(S.IdTransaction) "Items Archived",
       SUM(CAST(S.ItemSize AS bigint))/1024/1024 "Size of Items (GB)"
FROM   EnterpriseVaultDirectory.dbo.FileServerEntry FSE,
       EnterpriseVaultDirectory.dbo.FileServerVolumeEntry FSVE,
       EnterpriseVaultDirectory.dbo.FileServerVolumeArchiveEntry FSVAE,
       YourVaultStore.dbo.ArchivePoint AP,
       YourVaultStore.dbo.Saveset S
WHERE  S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND  AP.ArchivePointId = FSVAE.ArchiveVEID
  AND  FSVAE.VolumeEntryId = FSVE.VolumeEntryId
  AND  FSVE.FileServerEntryId = FSE.FileServerEntryId
GROUP BY FSE.DnsName

Comments

JesusWept3

Trusted Advisor

Nov

2012

SOLUTION

I think this query should

I *think* this query should work, its not tested but it works in theory, change "YourVaultStore" to the name of your vault store db that holds FSA Archives

SELECT FSE.DnsName "File Server",
       COUNT(S.IdTransaction) "Items Archived",
       SUM(CAST(S.ItemSize AS bigint))/1024/1024 "Size of Items (GB)"
FROM   EnterpriseVaultDirectory.dbo.FileServerEntry FSE,
       EnterpriseVaultDirectory.dbo.FileServerVolumeEntry FSVE,
       EnterpriseVaultDirectory.dbo.FileServerVolumeArchiveEntry FSVAE,
       YourVaultStore.dbo.ArchivePoint AP,
       YourVaultStore.dbo.Saveset S
WHERE  S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND  AP.ArchivePointId = FSVAE.ArchiveVEID
  AND  FSVAE.VolumeEntryId = FSVE.VolumeEntryId
  AND  FSVE.FileServerEntryId = FSE.FileServerEntryId
GROUP BY FSE.DnsName

Nov

2012

Thanks JW, very helpful query

Thanks JW, very helpful query indeed! Appreciate your help :-)