06-22-2015 03:03 PM
Hi,
Looking for the best way to do some trend analysis for EV. So i'd like to know the volume of data we'll archive in a year if we continue at the current rate? Or something similar.
We have the reporting services installed so what are the best ways to measure this?
Solved! Go to Solution.
06-22-2015 05:07 PM
I tend to use these, just change the count in the where clause to fit your needs
--Hourly Rate
select "Archived Date" = left (convert (varchar, archiveddate,20),14),"Hourly Rate" = count(*),"Size" =sum(Convert(bigint,itemsize))/1024
from saveset
where archiveddate > dateadd("dd", -7, getdate ())
group by left(convert (varchar, archiveddate,20),14)
order by "Archived Date" Desc
--Daily Rate
select "Archived Date" = left (convert (varchar, archiveddate,20),10),"Daily Rate" = count(*),"Size" =sum(Convert(bigint,itemsize))/1024
from saveset
where archiveddate > dateadd("dd", -30, getdate ())
group by left(convert (varchar, archiveddate,20),10)
order by "Archived Date" Desc
--Monthly Rate
select "Archived Date" = left (convert (varchar, archiveddate,20),7),"Monthly Rate" = count(*),"Size" =sum(Convert(bigint,itemsize))/1024
from saveset
where archiveddate > dateadd("mm", -12, getdate ())
group by left(convert (varchar, archiveddate,20),7)
order by "Archived Date" Desc
--Yearly Rate
select "Archived Date" = left (convert (varchar, archiveddate,20),4),"Yearly Rate" = count(*),"Size" =sum(Convert(bigint,itemsize))/1024
from saveset
where archiveddate > dateadd("YY", -10, getdate ())
group by left(convert (varchar, archiveddate,20),4)
order by "Archived Date" Desc
06-22-2015 04:32 PM
if you have reporting services then you're good to go but you could also use one of the many sql queries taht have been posted here which would give you what you're asking for
06-22-2015 05:07 PM
I tend to use these, just change the count in the where clause to fit your needs
--Hourly Rate
select "Archived Date" = left (convert (varchar, archiveddate,20),14),"Hourly Rate" = count(*),"Size" =sum(Convert(bigint,itemsize))/1024
from saveset
where archiveddate > dateadd("dd", -7, getdate ())
group by left(convert (varchar, archiveddate,20),14)
order by "Archived Date" Desc
--Daily Rate
select "Archived Date" = left (convert (varchar, archiveddate,20),10),"Daily Rate" = count(*),"Size" =sum(Convert(bigint,itemsize))/1024
from saveset
where archiveddate > dateadd("dd", -30, getdate ())
group by left(convert (varchar, archiveddate,20),10)
order by "Archived Date" Desc
--Monthly Rate
select "Archived Date" = left (convert (varchar, archiveddate,20),7),"Monthly Rate" = count(*),"Size" =sum(Convert(bigint,itemsize))/1024
from saveset
where archiveddate > dateadd("mm", -12, getdate ())
group by left(convert (varchar, archiveddate,20),7)
order by "Archived Date" Desc
--Yearly Rate
select "Archived Date" = left (convert (varchar, archiveddate,20),4),"Yearly Rate" = count(*),"Size" =sum(Convert(bigint,itemsize))/1024
from saveset
where archiveddate > dateadd("YY", -10, getdate ())
group by left(convert (varchar, archiveddate,20),4)
order by "Archived Date" Desc