cancel
Showing results for 
Search instead for 
Did you mean: 

EV archiving report

Wilson_H
Level 4

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
 

1 ACCEPTED SOLUTION

Accepted Solutions

JesusWept3
Level 6
Partner Accredited Certified

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 down

Also 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?

https://www.linkedin.com/in/alex-allen-turl-07370146

View solution in original post

6 REPLIES 6

Wayne_Humphrey
Level 6
Partner Accredited Certified

~11,000 results on mine in no time quick as hell ;)

Whats your SQL resorces like? CPU, Memory?

LCT
Level 6
Accredited Certified

I just ran that as well and it ran OK and quite quick too, returned about 7,500. Like Wayne said, it depends on how beefy your SQL server is.

Rob_Wilcox1
Level 6
Partner

It might also be helpful to know:

 

EV Version + Service Pack

Windows version + Service Pack

SQL version + Service Pack

 

Running it locally through SQL Mgmt Studio?

Working for cloudficient.com

Wilson_H
Level 4

Hi, the server is high utilization as shown in Pic. will this causing the server not responding to the query? or it return enormous result ?

 

Dushan_Gomez
Level 6

I'll try the script tomorrow in my office and will let you know the result.

JesusWept3
Level 6
Partner Accredited Certified

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 down

Also 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?

https://www.linkedin.com/in/alex-allen-turl-07370146