Video Screencast Help
Scheduled Maintenance: Symantec Connect is scheduled to be down Saturday, April 19 from 10am to 2pm Pacific Standard Time (GMT: 5pm to 9pm) for server migration and upgrades.
Please accept our apologies in advance for any inconvenience this might cause.

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!