Video Screencast Help

Question around SQL query

Created: 11 Jul 2013 • Updated: 28 Jul 2013 | 9 comments
Bruce Crankshaw2's picture
This issue has been solved. See solution.

Hi All

I have a customer that has been running EV since 2005 and during that time  some Journal Data was stamped with the wrong Retention Category. The Journal RC is 7 years and the Mailbox RC is 3 years. This incorrect marking of Journal data occurred in 2006 or 2007. The customer uses a Centera for storage of items running in Basic mode with Collections enabled. The customer has multiple Journal VS but only one Mailbox VS

Anyway the result is now when you implement Storage Expiry some Journal Data gets removed  before it is suppose to. I am trying to think of ways to implement Storage Expiry without the customer losing Journal Data

I found a query that will show me how much data in each  VS is marked by what RC, see the link below

But is there a way to see when this data was stored? From there we can say that means this data will be removed in Month xxx year xxx so we can at  least position how much Journal data will be deleted before its time?

Also is there an alternative way to delete this data from EV without Journal data being removed. I was thinking about changing the Retention Period but I am not sure how this would work in this scenario  or even be effective

Thanks :)

Operating Systems:

Comments 9 CommentsJump to latest comment

JesusWept3's picture

On the journal archive properties, uncheck the Dete items automatically

The journal archive will then be excluded from expiry

Bruce Crankshaw2's picture

Hi Alex

I thought about that but there may be issue as the customer does need to delete data and by unchecking that box it means we won't be able to delete much data at all as in the Centera due to its own  SIS there will be references to a blob that contains both the RC of Mailbox and Journal data. The Centera won't allow an item to be removed until the CDF has no more  references to it.

The customer does want to delete Journal data but not before its time. I hope this makes sense.

But your suggestion would allow us to delete Mailbox data at least so that may be the only option

I know you are skilled in SQL, is there a query that you can  run that will show when an item was archived based on the Retention Category? This is probably unlikely as the result would show millions of results but I can only ask :)

GertjanA's picture

Hello Bruce,

I believe there are some 3rd party tools that might be helpfull.

And I also believe that has something like this.

Thank you, Gertjan, MCSE, MCITP,MCTS, SCS, STS

Bruce Crankshaw2's picture

Thanks Gert, I'll look into this as an option.

I know this will be a cost to the customer but they need to weigh this cost aagainst purchasing more disk for the Centera

mswarz's picture

Hi Bruce,

I am with Globanet and wanted to see if you are interested in speaking to someone about our Globanet Classify solution.  What is the best number to reach you? 

Best, Michael 

JesusWept3's picture

This is the most ridiculous SQL Query i've ever written, but here you go

DECLARE @today dateTime
DECLARE @expiryBegins DateTime
DECLARE @retentionCategoryIdentity int
DECLARE @retentionPeriod int
DECLARE @retentionPeriodUnits varchar(6)
DECLARE @expiryBasis varchar(12)
DECLARE @expiryReallyShouldBegin DateTime
DECLARE @addedExpiryYears int
DECLARE @retentionCategoryName varchar(100)

SET @retentionCategoryName = 'My Retention Category'
SET @today = GETDATE()
SET @addedExpiryYears = 2

SELECT @retentionCategoryIdentity = retentionCategoryIdentity,
       @retentionPeriod = RetentionPeriod,
       @retentionPeriodUnits = 
       CASE RetentionPeriodUnits
       WHEN 0 THEN 'DD'
       WHEN 1 THEN 'WW'
       WHEN 2 THEN 'MM'
       WHEN 3 THEN 'YY'
       @expiryBasis = 
       CASE ExpiryDateBasis
       WHEN 0 THEN 'idDateTime'
       WHEN 1 THEN 'ArchivedDate'
       WHEN 2 THEN 'ArchivedDate'
FROM EnterpriseVaultDirectory.dbo.RetentionCategoryEntry
WHERE RetentionCategoryName = @retentionCategoryName

IF @retentionPeriodUnits = 'DD'
  SET @expiryBegins = DATEADD(DD, -@retentionPeriod, @today)
ELSE IF @retentionPeriodUnits = 'WW'
  SET @expiryBegins = DATEADD(WW, -@retentionPeriod, @today)
ELSE IF @retentionPeriodUnits = 'MM'
  SET @expiryBegins = DATEADD(MM, -@retentionPeriod, @today)
ELSE IF @retentionPeriodUnits = 'YY'
  SET @expiryBegins = DATEADD(YY, -@retentionPeriod, @today)

SET @expiryReallyShouldBegin = DATEADD(YY, -@addedExpiryYears, @expiryBegins)

IF @expiryBasis = 'idDateTime'
  SELECT DATEPART(YY, idDateTime) "Year",
         DATEPART(MM, IdDateTime) "Month",
         COUNT(IdTransaction) "Items To Expire",
         SUM(CAST(ItemSize AS bigint))/1024 "Size To Expire (MB)"
  FROM   EVVSYourVaultStore_1.dbo.Saveset 
  WHERE  IdDateTime <= @expiryReallyShouldBegin 
    AND  IdDateTime >= @expiryBegins 
    AND  RetentionCategoryIdentity = @retentionCategoryIdentity 
 ORDER BY "Year", "Month"
ELSE IF @expiryBasis = 'ArchivedDate'
  SELECT DATEPART(YY, ArchivedDate) "Year",
         DATEPART(MM, ArchivedDate) "Month",
         COUNT(IdTransaction) "Items To Expire",
         SUM(CAST(ItemSize AS bigint))/1024 "Size To Expire (MB)"
  FROM   EVVSYourVaultStore_1.dbo.Saveset 
  WHERE  ArchivedDate <= + @expiryReallyShouldBegin
    AND  ArchivedDate >= @expiryBegins
    AND  RetentionCategoryIdentity = @retentionCategoryIdentity 
 GROUP BY DATEPART(YY, ArchivedDate), DATEPART(MM, ArchivedDate) 
 ORDER BY "Year", "Month"

Bruce Crankshaw2's picture

Thanks for the response guys

@ Michael

I am seeing the customer next week so I'll contact you based on what they say. Also Jason Jacobo is a regular contributor to the SymIQ Archiving  distribution list that I'm part of so I can also chat to him

@ Alex

Thanks for this, you say the query is ridiculous? Can you comment on what you mean by ridiculous. Is it my question or the customers request or the fact the query will output too much data or am I just missing something by your usage of the word  ridiculous?

JesusWept3's picture

Nono it's an overly complex query for what should be a simple task
Just know that these are obviously not exact numbers especially when it comes to size,

The thing is, if it's just one archive and swapping one retcat to another there may be simpler ways to accomplish this task

Bruce Crankshaw2's picture

Thanks Alex, I'll update this post around what transpires at this cusotmer