EV archiving report
Dear All,
below script failed to run again, it is showing debuging and running a very long time without and result.
Output showing
Auto-attach to process '[1868] [SQL] Server' on machine 'server' succeeded.
SELECT A.ArchiveName,
COUNT(S.IdTransaction) "Archived Items"
FROM EnterpriseVaultDirectory.dbo.Archive A,
EnterpriseVaultDirectory.dbo.Root R,
yourVaultStore.dbo.ArchivePoint AP,
yourVaultStore.dbo.Saveset S
WHERE S.ArchivePointIdentity = AP.ArchivePointIdentity
AND AP.ArchivePointId = R.VaultEntryId
AND R.RootIdentity = A.RootIdentity
AND S.ArchivedDate > DATEADD(d, -3, getDate())
GROUP BY A.ArchiveName
ORDER BY A.ArchiveName
Take the "ORDER BY" out, and also you may want to look at things like index fragmentation and if you're rebuilding the indexes and other SQL Maintainance.
I do recall that EV9 Database upgrades run some queries like this extremely slowly in some circumstances, mos tlikely because of a removed or an altered index, but to be honest they shouldn't have to exist just to cater to these ad-hoc queries.
But really if you do a SQL Profiler and show the execution plans, you'll probably find that it is the ORDER BY that is causing the most significant slow downAlso is the Vault Store and the Directory database located on the same server, or are you using server linking and altering the query to call the secondary server?
