Video Screencast Help
Give us your opinion and win with Symantec! Please help us by taking this survey to tell us about your experience with Symantec Connect, so that we can continue to grow and improve.  Take the survey.

How to view archive rate using SQL query

Created: 12 Jul 2011 • Updated: 12 Jul 2011 | 7 comments
be-nugget's picture
This issue has been solved. See solution.

Hello,

I am looking for an SQL query to run against an EV9 DB to tell me what the archival rate of items is, whether this be a day or hour.

I did look at the performance guide I was given plus the link here http://www.symantec.com/connect/forums/how-report-your-archive-rate

But the saveset item if references cannot be found as a table and states an Invalid Object name, nor can I find saveset anywhere. I wonder if this is for a version of EV pre version 9.

Could someone assist? I am not a DBA but I can understand the sql query basics if there are given to me.

Reporting services is not an option unfortunately. But I am pretty sure with the right query I can get this info.

Comments 7 CommentsJump to latest comment

Mohawk Marvin's picture

The first query will give you the number of items, size of the archived items and start and end duration of archiving.

useevvaultstore1

Selectcount(*)as "Number of Items",sum(itemsize)/1024 as "Size of the items(MB)", min(archiveddate) as "Start Date",max(archiveddate) as "End Date" from saveset where archiveddate >'2009-01-01 18:00' and archiveddate < '2009-09-15 23:59'

The second query will give you the daily archiving rate for the last 10 days

useevvaultstore1

select"Archived Date" = left (convert (varchar, archiveddate,20),10),"Daily Rate" = count (*),"Size" = sum (itemsize)from saveset

wherearchiveddate > dateadd("hh", -240, getdate ())

groupby

left(convert (varchar, archiveddate,20),10)

orderby "Archived Date"

Desc

SOLUTION
Mohawk Marvin's picture

you need to use the vault store in your environment dude smiley

So something like

Use MohawkMarvinsVaultStore

etc etc etc

be-nugget's picture

When I try and run the sql query against the EnterpriseVaultDirectory DB and change the use statement to the name of my vault store it states

 Could not locate entry in the sysdatabases for database 'testvaultstore'. No entry found with that name.

So the statement looks like this:

use testvaultstore

Select count(*)as "Number of Items",sum(itemsize)/1024 as "Size of the items(MB)", min(archiveddate) as "Start Date",max(archiveddate) as "End Date" from saveset where archiveddate >'2009-01-01 18:00' and archiveddate < '2009-09-15 23:59' 

Mohawk Marvin's picture

ok so delete the use statement from my query, and run the query against TestVaultStore or attach a pic or you SQL DB's as the query works for me :)

Mohawk Marvin's picture

Oh and change the dates to suit you as well as you are still using 2009 atm

be-nugget's picture

Right got it now - just removed the use statement - looks like i'm cooking on gas now!