SQL livereport to get size of recycle bin

Hi,

I am looking for a livereport which gives me the size occupied by documents in recycle bin. Database I want to run in is SQL .

Thanks,

Atiya

Find more posts tagged with

Comments

Matthew_Barben

I would suggest you will also need to calculate the size of versions as well (as looking up document size will only confirm the latest version size).

Lindsay_Davies

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

Hi Matthew,

I was ahead of you here.

The query does indeed sum the size of all versions.

It is doing that from DVersData.

We do not store a size on DTree, only on DVersData.

Regards

Lindsay

OpenText UK

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 2011 November 25, Fri 00:11
To: eLink Recipient
Subject: SQL livereport to get size of recycle bin

SQL livereport to get size of recycle bin

Posted by mbarben@goldcoast.qld.gov.au (Barben, Matthew) On 11-24-2011 19:06

I would suggest you will also need to calculate the size of versions as well (as looking up document size will only confirm the latest version size).

[To post a comment, use the normal reply function]
Topic:	SQL livereport to get size of recycle bin
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Atiya_Sultana

Hi,

I have tried the below, but it gives error "GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression 'subtree'". Can you please advise what is wrong here? I don't have access to SQL query analyzer so need to get it right before asking someone to run it for me.

With subtree (dataid, datasize)
as (select dt.dataid, dv.datasize from dtree dt (nolock), dversdata dv (nolock) where dt.dataid=dv.docid and dt.dataid=xxxx
union all select s.dataid, sum(s.datasize/1024/1024) from dtree p (nolock), dversdata dp(nolock), subtree s (nolock) where s.dataid=p.dataid and s.dataid=dp.docid group by s.dataid)
select dataid, sum(datasize/1024/1024) from subtree (nolock)

Thanks,

Atiya

Colin_Schmidt

The DTREE.ownerid = -4614 represents entries in the RecycleBin. You'll have to check that, as well.

Colin_Schmidt

This is in Oracle SQL:

select round((sum(dv.datasize)/1048576), 2) "RecycleBin"
from livelink.dtree dt
join livelink.dversdata dv on dt.dataid = dv.docid
where dt.ownerid = -4614 and dt.subtype <> 0

It starts with DTREE checking the OWNERID and SUBTYPE not a Folder and then extracts all DVERSDATA based on the DTREE.dataid = DVERSDATA.docid relationship and rounds the results to 2 decimal places(obviously optional). I don't need a "group by".

Lindsay_Davies

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

Hi Atiya,

You do not need a statement as complex as this.

The statement that I gave you last week will give the same summed size as using a recursive CTE would, if you could write a working one.

There are several mistakes in your statement.

As the DB error says, you cannot put a group by in the recursive part of a CTE.

That means you need to remove the group by and the aggregate function [ sum(s.datasize/1024/1024) ] part from within the CTE.

The first part of a CTE needs to return a row that has values for the join condition in the second part.

Your first part will not return a row [ so the second part will not work either ].

select dt.dataid, dv.datasize from dtree dt (nolock), dversdata dv (nolock) where dt.dataid=dv.docid and dt.dataid=xxxx

This needs select two values (dataid and datasize) for the RecycleBin itself, assuming that is the DataID you will pass in.

But as a container object it does not have a DVersData row - therefore this select will not return any rows.

That leads us on to the second part of the CTE...

select s.dataid, sum(s.datasize/1024/1024) from dtree p (nolock), dversdata dp(nolock), subtree s (nolock) where s.dataid=p.dataid and s.dataid=dp.docid group by s.dataid

You cannot sum the size here because you cannot do a group by here, so it has to be done in the final select from subtree.So this bit would have to read....

select s.dataid, s.datasize from dtree p (nolock), dversdata dp(nolock), subtree s (nolock) where s.dataid=p.dataid and s.dataid=dp.docid

This makes selecting the datasize here rather pointless when the CTE is being recursed.

You are not joining the subtree to DTree correctly.

The first time round the imaginary loop, the information is from the first select.

So that dataid is matched with the parentid from DTree in the second select.

That means it would need s.dataid=p.parentid.

If what is found in the recycle bin is a folder/container, again you will not get a match on the join to DVersData, so you have no row and processing would stop. So it actually stops at the first year folder.

Basically, you cannot have DVersData involved inside your particular CTE (i.e. for your example).

That means you would have to only join to DVersData on the last select statement.

So your CTE would only need to generate all the DataIDs that exist within the RecycleBin container.

As you want to find all the items in the workspace, using a CTE is unnecessary - all items in a workspace have the same ownerID.

This is column name is not about a User but it the owning location (volume/workspace).

You would use a CTE for walking a hierarchy within a volume/workspace (where that is only part of the workspace content).

It is all way too much trouble and will consume more resources than the original statement I suggested.

I will not rewrite / correct your statement to make it work as it will look nothing like what you have given as input.

It is not the way to do this.

There are examples of working CTEs elsewhere in this discussion that you can look at.

Regards

Lindsay

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 2011 November 28, Mon 11:55
To: eLink Recipient
Subject: RE SQL livereport to get size of recycle bin

RE SQL livereport to get size of recycle bin

Posted by atiya.sultana@accenture.com (Sultana, Atiya) On 11-28-2011 06:51

Hi, I have tried the below, but it gives error "GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression 'subtree'". Can you please advise what is wrong here? I don't have access to SQL query analyzer so need to get it right before asking someone to run it for me. With subtree (dataid, datasize)
as (select dt.dataid, dv.datasize from dtree dt (nolock), dversdata dv (nolock) where dt.dataid=dv.docid and dt.dataid=xxxx
union all select s.dataid, sum(s.datasize/1024/1024) from dtree p (nolock), dversdata dp(nolock), subtree s (nolock) where s.dataid=p.dataid and s.dataid=dp.docid group by s.dataid)
select dataid, sum(datasize/1024/1024) from subtree (nolock) Thanks, Atiya

[To post a comment, use the normal reply function]
Topic:	SQL livereport to get size of recycle bin
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Lindsay_Davies

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

This is a better attempt and is rather like the one I posted last week.

On a generic system, you could get other container subtypes in recycle bin as well as folders.

Both statements are portable between the relational database server types Oracle and SQL server.

As a comparison of how they perform, I ran your join statement and my nested select statement against the same sql server db for a small instance.

For better numbers I used the Enterprise workspace not the recycle bin.

select round((sum(dv.datasize)/1048576), 2) "RecycleBin"
from dtree dt
join dversdata dv on dt.dataid = dv.docid
where dt.ownerid = -2000 and dt.subtype <> 0select round((sum(datasize)/1048576), 2) "RecycleBin" from dversdata where docid in (select dataid from dtree where ownerid=-2000)

The execution plans showed the Query Cost relative to the batch (i.e. both together) as 60% for the join and 40% for the nested select.

Regards

Lindsay

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 2011 November 28, Mon 13:40
To: eLink Recipient
Subject: RE SQL livereport to get size of recycle bin

RE SQL livereport to get size of recycle bin

Posted by colin.schmidt@almacgroup.com (Schmidt, Colin J) On 11-28-2011 08:35

This is in Oracle SQL: select round((sum(dv.datasize)/1048576), 2) "RecycleBin"
from livelink.dtree dt
join livelink.dversdata dv on dt.dataid = dv.docid
where dt.ownerid = -4614 and dt.subtype <> 0 It starts with DTREE checking the OWNERID and SUBTYPE not a Folder and then extracts all DVERSDATA based on the DTREE.dataid = DVERSDATA.docid relationship and rounds the results to 2 decimal places(obviously optional). I don't need a "group by".

[To post a comment, use the normal reply function]
Topic:	SQL livereport to get size of recycle bin
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Atiya_Sultana

Thanks Colin and Lindsay for your response. It did help me easily get the size of the deleted data. Thanks much for that.

Can you or anyone please help me with below queries as well? The first one gets me the size of deleted documents in recycle bin. The second one gives me size of deleted documents from each external file store. But when I add the size output from each EFS in query 2, it doesn't equal to the size in query 1. Not sure what is going wrong here.

1) select (sum(convert(bigint,dv.datasize))/1024/1024/1024) as 'Size of RB in GB', pd.providertype from DTree dt (nolock), DVersdata dv (nolock), ProviderData pd (nolock) where dv.docid=pd.providerid and dt.dataid=pd.providerid and dt.ownerid = -xxxx and dt.subtype = 144 group by dp.providertype

2) select (sum(convert(bigint,dv.datasize))/1024/1024/1024) as 'Size of RB in GB' from DTree dt (nolock) join DVersdata dv (nolock) on dt.dataid = dv.docid where dt.ownerid = -xxxx and dt.subtype = 144

Appreciate your help.

Thanks,

Atiya

Lindsay_Davies

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

Hi Atiya,

I'm sorry to say this discrepancy is because your first statement is rubbish as far as the schema goes!

It is a classic example of "garbage in, garbage out".

It does not make sense to join tables on arbitrary columns.

DTree has DataID

which is used as a foreign key in DVersData as the DocID column

ProviderData has ProviderID

which is used as a foreign key in DVersData as the ProviderID column (i.e. same name)

Therefore your statement is wrong...

select (sum(convert(bigint,dv.datasize))/1024/1024/1024) as 'Size of RB in GB', pd.providertype from DTree dt (nolock), DVersData dv (nolock), ProviderData pd (nolock)

where

dv.docid=pd.providerid <---- this is wrong - you have picked a wrong column in DVersData as a foreign key in ProviderData

and dt.dataid=pd.providerid <---- this is wrong - you cannot joining DTree and ProviderData because there are no matching columns

and dt.ownerid = -xxxx and dt.subtype = 144 group by dp.providertype <--- typo:- dp.providertype should be pd.providertype

The reason your answer is (presumably) close rather than zero is because for version 1 of a document the following is true providerID=versionid=docid - so your bad joining effectively is only summing version 1 sizes.

so the two lines I highlight should be

dv.providerid=pd.providerid and dt.dataid=dv.docid Finally, you will encounter minor rounding problems as you are going to add GB values. you could output a straight sum(datasize) in both selects so this retains the accurate values.

Regards

Lindsay

OpenText UK

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 2011 December 05, Mon 12:41
To: eLink Recipient
Subject: RE RE SQL livereport to get size of recycle bin

RE RE SQL livereport to get size of recycle bin

Posted by atiya.sultana@accenture.com (Sultana, Atiya) On 12-05-2011 07:31

Thanks Colin and Lindsay for your response. It did help me easily get the size of the deleted data. Thanks much for that. Can you or anyone please help me with below queries as well? The first one gets me the size of deleted documents in recycle bin. The second one gives me size of deleted documents from each external file store. But when I add the size output from each EFS in query 2, it doesn't equal to the size in query 1. Not sure what is going wrong here. 1) select (sum(convert(bigint,dv.datasize))/1024/1024/1024) as 'Size of RB in GB', pd.providertype from DTree dt (nolock), DVersdata dv (nolock), ProviderData pd (nolock) where dv.docid=pd.providerid and dt.dataid=pd.providerid and dt.ownerid = -xxxx and dt.subtype = 144 group by dp.providertype 2) select (sum(convert(bigint,dv.datasize))/1024/1024/1024) as 'Size of RB in GB' from DTree dt (nolock) join DVersdata dv (nolock) on dt.dataid = dv.docid where dt.ownerid = -xxxx and dt.subtype = 144 Appreciate your help. Thanks, Atiya

[To post a comment, use the normal reply function]
Topic:	SQL livereport to get size of recycle bin
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Atiya_Sultana

Hi Lindsay,

I know that was too mismatching. Thanks for pointing that out. Its working fine now.

Thanks for your help,

Atiya