Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

Enterprise Vault useful SQL queries

Created: 26 Aug 2010 • Updated: 04 Nov 2010 | 29 comments
Language Translations
Liam Finn's picture
+8 8 Votes
Login to vote

I have worked some time on EV and due to encountering many issues which were hidden from day to day system checks we developed some interesting and useful SQL queries which when used can keep you better informed on whats going on with your EV environment

This is just a sample of some of the more useful queries we use regularly

As an example This query will find the index folder name of a given index Volume. This can be very useful when you are looking for the elusive indexmissing.log files

** Database: EnterpriseVaultDirectory
** Table: dbo.IndexVolume
** Requirements: An index volume number
** Directions: Enter the index volume where '999' exists
**
*/

SELECT IndexVolumeIdentity AS 'Index Volume', FolderName AS 'Index Folder Name'
FROM indexvolume
WHERE indexvolumeidentity = '999'

Here is another that will list all the EV servers the Alias names of the servers and also the version of EV on each server. This can be very useful in large environments to keep track on how your upgrades are going or to validate you completed your upgrades before turning on the services. I find it to be a useful check to have

** Description: List the EV servers, the alias and EV version.
**
**
** Requirements: Run against the appropriate vault store database.
**
*/

SELECT ComputerNameAlternate AS Hostname, ComputerName AS Alias, VaultSoftwareVersion AS 'EV Version'
FROM ComputerEntry
ORDER BY ComputerNameAlternate

This helps you identify the Archive date of an item once you have it's transation ID

 

** Description: Returns the date an item was archived for a given IDTransaction.
**
** Requirements: Run against the appropriate vault store database.
**
*/

SELECT archiveddate
FROM saveset
WHERE idtransaction = '770F689C-F7F4-4C9D-B87D-D558B4F70F00'

For those with may retention catagories and users broken into so many retention catagories this little query will provide you the retention catagory that has bee assigned to a specific Vault Store. 

 

** Description: Find Retention Category Name and Retention Category
** Identity for a given Vault Store.
**
** Requirements: Run against the appropriate vault store database.
**
*/

SELECT DISTINCT RC.RetentionCategoryName, S.RetentionCategoryIdentity
FROM EnterpriseVaultDirectory.dbo.RetentionCategoryEntry RC, EVHISTORICALEMail07.dbo.saveset S
WHERE S.RetentionCategoryIdentity = RC.RetentionCategoryIdentity

If you need to generate a list of the Vault Stores, their archives, the associated Database and even the name of the server they are associated with this little query will give you all you need in one nice report

**  Description:                List of vault stores, archives, database name and SQL server.
**                                                                          
**                                                      
**  Requirements:          Run against the EnterpriseVaultDirectory database
**             
*/
 
SELECT VaultStoreName AS 'Vault Store', ArchiveName AS Archive, DatabaseDSN AS 'Database', SQLServer AS 'SQL Server'
FROM VaultStoreEntry vse, Archive a
WHERE vse.VaultStoreEntryId = a.VaultStoreEntryId
GROUP BY VaultStoreName, ArchiveName, DatabaseDSN, SQLServer

This little query returns the number of items archived in a specific Vault Store between a given date and time

**  Description:                Returns the number of items archived for a given date range.                   
**                                                      
**  Requirements:          Run against the appropriate vault store datagase.
**             
*/
SELECT COUNT(*) AS 'No. of Items Archived'
FROM saveset
--WHERE archiveddate > '1980-01-01 00:00:00.000' and archiveddate < '2009-06-24 00:00:00.000'

Here are two that are scheduled to run using Sql Reporting Services and provide insight into the EV Environment

This first one provides you with the backup state of your EV servers. We run it before the backup is supposed to happen, just after kickoff of the backup and again after the backup completes to ensure that nothing is in backup state before the backup, everything is in backup state during the backup and that everything is removed from the backup state after the backup

**  Description:  The query reports on the Backup Mode state for the Enterprise
**                                                             Vault indexs.
**               
**
**  Requirements:  Run against the EnterpriseVaultDirectory database.
**                         
*/
 
 
 
SELECT CE.ComputerNameAlternate AS 'Hostname', CE.ComputerName AS 'Alias', IRPE.BackupMode
FROM dbo.IndexRootPathEntry AS IRPE, ComputerEntry AS CE, IndexingServiceEntry AS ISE
WHERE ISE.ServiceEntryId = IRPE.IndexServiceEntryId AND CE.ComputerEntryID = ISE.ComputerEntryID
ORDER BY CE.ComputerNameAlternate

This second scheduled query will provide you with Index information. It will tell you if you have any failed to index items and also if your index are in and Offline, Failed or Rebuilding state

This little one we run at least twice a day to keep a watchful eye on the indexes to ensure health

SELECT ArchiveName AS 'Archive', FolderName AS 'Folder', [Offline] AS 'Index Offline', Failed AS 'Index Failed', Rebuilding AS 'Index Rebuilding',
failedItems AS 'Failed Items'
FROM Archive a, IndexVolume iv 
WHERE a.RootIdentity = iv.RootIdentity and
([Offline] = 'True' or Failed = 'True' or Rebuilding = 'True' or failedItems <> '')
SELECT ArchiveName AS 'Archive', FolderName AS 'Folder', [Offline] AS 'Index Offline', Failed AS 'Index Failed', Rebuilding AS 'Index Rebuilding',
failedItems AS 'Failed Items'
FROM Archive a, IndexVolume iv 
WHERE a.RootIdentity = iv.RootIdentity and
([Offline] = 'True' or Failed = 'True' or Rebuilding = 'True' or failedItems <> '')

Now it is time to take a break from EV and lets look at a simple SQL query which checks the status of your SQL maintenance jobs.

As we all know these are a vital part of the health of your SQL environment and keeping track that the maintenance jobs completed successfully is important so it is goo to schedule this to run each day using SQL Reporting Services so it emails you the results and from your inbox you can see the health of your SQL maintenance jobs

**  Description:                Designed to return, for the current date, the result of the 
**                                                                           individual backup maintenance jobs                       
**                                                      
**  Requirements:  Run against the msdb system database.            
**             
*/
 
USE msdb
SELECT sj.name AS 'Job Name', sjh.server AS 'SQL Server', LEFT(sjh.message, 20) AS Result, convert(varchar, sjh.run_date) AS 'Run Date'
FROM sysjobs AS sj, sysjobhistory AS sjh
WHERE sj.job_id = sjh.job_id and sjh.step_name = '(Job outcome)'
and ltrim(rtrim(convert(varchar,getdate(),112))) = ltrim(rtrim(convert(varchar,run_date,112)))
--and sjh.run_date >= '20080921'
GROUP BY sj.name, sjh.server, sjh.message, sjh.run_date
ORDER BY sjh.run_date DESC

And another SQL query which will provide a list of your SQL databases and the size of each database

USE MASTER
EXEC
SP_database

Now was that not easy. See SQL is not as much as a minefield as many believe

As you can see most of this was directed at EV in General and also some SQL system included. I do however plan on putting together a similar Article on SQL queries directed toward Discovery Accelerator and maybe if you are nice I just might do one directed at Storage Expiry

Just remember any query that i said should be run using SQL Reporting Services (SRS) should be configured to be emailed out to the EV Admin from SRS so they can get automated info to help them manage their EV Environment

 

 

 

Comments 29 CommentsJump to latest comment

AndrewB's picture

very nice article

Andy Becker | Authorized Symantec Consultant | Trace3 | Symantec National Partner | www.trace3.com

0
Login to vote
Bruce Crankshaw's picture

Nice one Scanner :)

0
Login to vote
LanceChase's picture

**  Description:                This Query will sum up the items that were archived by week.                   
**                                                      
**  Requirements:  Updated the Vault databases as required, and update the Vault store identifier to match.    Change VaultstoreX to
**     your first vaultstore and VaultstoreY to your second, you can add as many as you need to get them all.        
**     DateDiff - we used this as our starting week, so we could see by week, buy you could change that to be the first day of the year
**     and report on weekly archiving.
**    then import into excel and create a pivot chart.

select "Vault Store" = 'VaultStoreX',

"Week Number" = DATEDIFF(week, '2008-10-01 00:00:00' , s.archiveddate),

"Date" = CAST(CONVERT(VARCHAR(10), s.archiveddate, 111) AS DATETIME),

"Items Archived" = count (*),

"Original Size (MB)" = sum (sp.originalsize)/1024/1024,

"Compressed Size (MB)" = sum (s.itemsize)/1024

from VaultStoreX.dbo.saveset S with (nolock)

INNER JOIN VaultStoreX.dbo.savesetproperty SP with (nolock) on S.savesetidentity = SP.savesetidentity

INNER JOIN VaultStoreX.dbo.Vault v with (nolock) on s.VaultIdentity = v.VaultIdentity

INNER JOIN VaultStoreX.dbo.ArchivePoint ap with (nolock) ON v.ArchivePointIdentity = ap.ArchivePointIdentity

group by DATEDIFF(week, '2008-10-01 00:00:00' , s.archiveddate),

CAST(CONVERT(VARCHAR(10), s.archiveddate, 111) AS DATETIME)

UNION ALL

select "Vault Store" = 'VaultStorey',

"Week Number" = DATEDIFF(week, '2008-10-01 00:00:00' , s.archiveddate),

"Date" = CAST(CONVERT(VARCHAR(10), s.archiveddate, 111) AS DATETIME),

"Items Archived" = count (*),

"Original Size (MB)" = sum (sp.originalsize)/1024/1024,

"Compressed Size (MB)" = sum (s.itemsize)/1024

from VaultStorey.dbo.saveset S INNER JOIN

VaultStorey.dbo.savesetproperty SP on S.savesetidentity = SP.savesetidentity

group by DATEDIFF(week, '2008-10-01 00:00:00' , s.archiveddate),

CAST(CONVERT(VARCHAR(10), s.archiveddate, 111) AS DATETIME)

order by 1,2,3

+2
Login to vote
LanceChase's picture

**  Description:                Archiving Rate by hour.                   
**                                                      
**  Requirements:  Updated the Vault databases as required, and update the Vault store identifier to match.    Change VaultstoreX to
**     your first vaultstore and VaultstoreY to your second, you can add as many as you need to get them all.        
**     Then import into excel and create a pivot chart.

select "Vault Store" = 'VaultStoreX',

"Archived Date" = left (convert (varchar, s.archiveddate,20),14)+'00',

"Hourly Rate" = count (*),

"MB (original)" = sum (sp.originalsize)/1024/1024,

"MB (compressed)" = sum (s.itemsize)/1024

from VaultStoreX.dbo.saveset S INNER JOIN

VaultStoreX.dbo.savesetproperty SP on S.savesetidentity = SP.savesetidentity

Where left(convert (varchar, s.archiveddate,20),14)+'00' > GETDATE() - 2

group by left (convert (varchar, s.archiveddate,20),14)

Union

select "Vault Store" = 'VaultStoreY',

"Archived Date" = left (convert (varchar, s.archiveddate,20),14)+'00',

"Hourly Rate" = count (*),

"MB (original)" = sum (sp.originalsize)/1024/1024,

"MB (compressed)" = sum (s.itemsize)/1024

from VaultStoreY.dbo.saveset S INNER JOIN

VaultStoreY.dbo.savesetproperty SP on S.savesetidentity = SP.savesetidentity

Where left(convert (varchar, s.archiveddate,20),14)+'00' > GETDATE() - 2

group by left (convert (varchar, s.archiveddate,20),14)

order by "Vault Store", "Archived Date" desc

+2
Login to vote
Matthew Kimler's picture

Here's a good one.

**  Description:    Return all vaults that have at least one failed item in them.                  
**                                                      
**  Requirements:          Run against the EnterpriseVaultDirectory database

** 

Select IndexRootPathEntryId, FolderName, FailedItems from IndexVolume
Where FailedItems > '0'
order by faileditems desc

 

Matthew Kimler 

0
Login to vote
Dushan Gomez's picture

 

Msg 208, Level 16, State 1, Line 1
Invalid object name 'IndexVolume'.
 
thanks for sharing anyway mate !

Dushan Gomez
IT Manager
VCP 4 and 5 | MCITP Exchange Server | MCTS SharePoint Server | MCP Windows XP

 

+1
Login to vote
Matthew Kimler's picture

I just tried it again and it works for me. Perhaps the copy and paste got something stuck in your, but I just confirmed at another customer site and it still works. 

Thanks!

 

Matthew Kimler 

+1
Login to vote
Rob.Wilcox's picture

Great article.  Thanks for taking the time to share it with the Community.

+1
Login to vote
Liam Finn's picture

Lets keep adding more queries. The more we share the more we all learn

+1
Login to vote
Dushan Gomez's picture

Yes, so how can we keep on updated with this very useful post ?

can someone pelase create a blog and then I'll subscribe to that :-) Please ...

Dushan Gomez
IT Manager
VCP 4 and 5 | MCITP Exchange Server | MCTS SharePoint Server | MCP Windows XP

 

+1
Login to vote
Rob.Wilcox's picture

Definitely a good idea.  I put a few on my Connect Blog...

https://www-secure.symantec.com/connect/blogs/rob-...

I'll be doing more relating to PST Migrations "soon".

0
Login to vote
John Santana's picture

Hi Rob,

your blog is not accessible ?

The requested page could not be found.

Kind regards,

John Santana
IT Professional

--------------------------------------------------

Please be nice to me as I'm newbie in this forum.

+1
Login to vote
AndrewB's picture

cheers to that and to the awesome experts sharing their scripts

Andy Becker | Authorized Symantec Consultant | Trace3 | Symantec National Partner | www.trace3.com

+1
Login to vote
Liam Finn's picture

Excellent work. I will be adding more soon directed at Discovery Accelerator. They will be on another Article so as not to get them mixed up with the others listed here. Lets keep this for EV core

+1
Login to vote
AndrewB's picture

can someone provide a sql script that will return the same data that you get from the vault console "status" ? particularly, i'd like to be able to query the last backup times.

Andy Becker | Authorized Symantec Consultant | Trace3 | Symantec National Partner | www.trace3.com

0
Login to vote
EVStrategist's picture

Is there a sql query to determine the 'total' number of items in an index. So as to determine if the index has reached the recommended 100 million items limit.

Thanks

Glenn.

0
Login to vote
AndrewB's picture

glenn, i learned it's probably best to request this in a new post in the forums.

Andy Becker | Authorized Symantec Consultant | Trace3 | Symantec National Partner | www.trace3.com

0
Login to vote
Liam Finn's picture

I have never tried such a query.....No promises but I will look into this.

 

I do wonder why you would need such a query seeing as you normally you would roll over the indexes

0
Login to vote
EVStrategist's picture

Thanks Andrew for positng so quickly...

I have one EV site with indexing thathas 8 locations on each server - most servers have over 1.3 , some over 2 tb of index volume space (D: split up in to 8 directories).  I want to see if some directories(each of the 8 index volumes) have space available for more indexing to be written to them.  If so I have two options:  1) continue to add more space to the D: increasing the total size even more over 2TB  or 2) create a new LUN and move one of the index locations to that LUN (Say F: or G:).  We currently add 300gb each time the space nears 90% full in order to not crash EV.   Over time the required free space (the 10% that we maintain) keeps growing as 10% of an ever increasing LUN (300, 600 then 900, 1.2tb, 1.5 tb and now 1.8+TB) grows from initially requiring only 30gb free, to 60Gb free, to 90Gb free to now 180Gb free to keep under 90% used.  This is a waste of space.  We have several LUNS that have 200-300 Gb free on them but because they are at or over 90% used we need to add more space. My thoughts were to either 1) close all 8 locations off on D:  and create a G: and add 8 new locations (LUN of intially 300Gb) and let it grow over time. or 2) Move a single Location (say maybe 250-300Gb-one of the current 8 locations) to G: .  This would free up 250+GB on D: and I would have one index location on G:  with free space to grow.  However if any of these 8 locations are nearing 100million items in them, then I wont move I will just close them off and create a G: and add a new index location as required to maintain 8 open index locations.  

0
Login to vote
EVStrategist's picture

running EV 9.02

0
Login to vote
GertjanA's picture

Credits to JesusWept!

I found this one, which is usefull to see if a changed PG/policy does work.

The output is:

Archived Date, Mailbox Name, PRovisioning Group, No of items archived, Archived item size (MB) and Original Items size (MB).

The last Where clause specifies to check for 1 day only. This can be changed obviously.

SELECT  left(convert (varchar, S.archiveddate,20),10) "Archived Date",
 EME.MbxDisplayName "Mailbox Name",
 PTG.DisplayName "Provisioning Group",
 COUNT(S.ItemSize) "No. Items Archived",
 SUM(S.ItemSize)/1024 "Archived Item Size (MB)",
 SUM(SP.OriginalSize)/1024/1024 "Original Item Size (MB)"
  
FROM YourVaultStore.dbo.Saveset S,
 YourVaultStore.dbo.SavesetProperty SP,
 YourVaultStore.dbo.ArchivePoint AP,
 EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry EME,
 EnterpriseVaultDirectory.dbo.PolicyTargetGroup PTG
    
WHERE S.SavesetIdentity = SP.SavesetIdentity
  AND   S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND AP.ArchivePointID = EME.DefaultVaultId
  AND   EME.PolicyTargetGroupEntryId = PTG.PolicyTargetGroupEntryId
  AND PTG.DisplayName = 'Provisioning Group Name'
  AND S.ArchivedDate > DATEADD(d, -1, getDate())

GROUP BY left(convert (varchar, archiveddate,20),10), EME.MbxDisplayName, PTG.DisplayName
ORDER BY PTG.DisplayName, EME.MbxDisplayName 

Thank you, Gertjan, MCSE, MCITP,MCTS, SCS, STS
Company: www.t2.nl

www.quadrotech-it.com

www.symantec.com/vision

+1
Login to vote
Dushan Gomez's picture

 

SELECT LEFT(CONVERT (VARCHAR, S.archiveddate, 20), 10) "Archived Date", 
       EME.mbxdisplayname                              "Mailbox Name", 
       PTG.displayname                                 "Provisioning Group", 
       Count(S.itemsize)                               "No. Items Archived", 
       Sum(S.itemsize) / 1024                          "Archived Item Size (MB)", 
       Sum(SP.originalsize) / 1024 / 1024 
       "Original Item Size (MB)" 
FROM   EVVaultstore1.dbo.saveset S, 
       EVVaultstore1.dbo.savesetproperty SP, 
       EVVaultstore1.dbo.archivepoint AP, 
       enterprisevaultdirectory.dbo.exchangemailboxentry EME, 
       enterprisevaultdirectory.dbo.policytargetgroup PTG 
WHERE  S.savesetidentity = SP.savesetidentity 
       AND S.archivepointidentity = AP.archivepointidentity 
       AND AP.archivepointid = EME.defaultvaultid 
       AND EME.policytargetgroupentryid = PTG.policytargetgroupentryid 
       AND PTG.displayname = 'Provisioning Group Name' 
       AND S.archiveddate > Dateadd(d, -1, Getdate()) 
GROUP  BY LEFT(CONVERT (VARCHAR, archiveddate, 20), 10), 
          EME.mbxdisplayname, 
          PTG.displayname 
ORDER  BY PTG.displayname, 
          EME.mbxdisplayname 
 
Why the result is empty when I execute that against my EVVaultStore1 DB ?

Dushan Gomez
IT Manager
VCP 4 and 5 | MCITP Exchange Server | MCTS SharePoint Server | MCP Windows XP

 

+1
Login to vote
Cdee's picture

Great artcile helped a lot thanks a ton!!

Sr Messaging Engg.

+1
Login to vote
John Santana's picture

thanks for sharing the information here guys !

Kind regards,

John Santana
IT Professional

--------------------------------------------------

Please be nice to me as I'm newbie in this forum.

0
Login to vote
Arco Ltd's picture

Hello 

Hoping one of you guys can help me, I used to use a post to retrospectively change already archived items in our EV vault.. This used to be published on a KB article, but for some reason it's now been taken down.

[code]
Use [VaultStoreDB]
Update Top (10000) Saveset set RetentionCategoryIdentity = [NewRetentionCategoryIdentity] where ArchivePointIdentity = [ArchivePointIdentity]

[/code]
 
Which has been working great, however - there was a second select statement to find the archivepointidentify from the mailbox archive ID. I can't seem to find this SQL command any more.. Can any of you guys assist?
 
Thanks
0
Login to vote
ITDAP_COM's picture

Could be this one Mike ?

 

SELECT @ArchivePointID = ArchivePointIdentity

FROM Vault

WHERE VaultID = @VaultID

If this response answers your concern, please mark it as a "solution"

0
Login to vote
Arco Ltd's picture

Thanks! Works perfectly :) 

0
Login to vote