on 08-26-2010 09:57 AM
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
**
**
**
** 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
**
**
** 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,
--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
Nice one Scanner :)
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
** 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
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
Great article. Thanks for taking the time to share it with the Community.
Lets keep adding more queries. The more we share the more we all learn
Definitely a good idea. I put a few on my Connect Blog...
https://www-secure.symantec.com/connect/blogs/rob-wilcox
I'll be doing more relating to PST Migrations "soon".
cheers to that and to the awesome experts sharing their scripts
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
Good idea.
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.
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.
glenn, i learned it's probably best to request this in a new post in the forums.
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
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.
running EV 9.02
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
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 ...
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!
Great artcile helped a lot thanks a ton!!
thanks for sharing the information here guys !
Hi Rob,
your blog is not accessible ?
The requested page could not be found.
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]
Could be this one Mike ?
SELECT @ArchivePointID = ArchivePointIdentity
FROM Vault
WHERE VaultID = @VaultID
Thanks! Works perfectly :)