Enterprise Vault useful SQL queries

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