You are viewing limited content. For full access, please sign in.

Question

Question

Is there a way to find out the recycle bin size of a repository?

asked on May 28, 2014

I didn't find any info relating to it. Please advise.

 

0 0

Answer

APPROVED ANSWER
replied on May 28, 2014 Show version history

Here is a SQL command based off of the LF server code for the "Total Document Size" entry listing column. It computes the total recycle bin size, including the total sum of all children under recycled folders.

 

select sum(recycle_bin_sizes.total_recycled_entry_size) as total from
(
 select (case when recycle_bin.orig_name is null then toc.name else recycle_bin.orig_name end) as name, toc.tocid,
 (
  (
   case when toc.edoc_storeid is null
   then 0
   else
    (
    case when toc.edoc_size is null
    then 0
    else toc.edoc_size
    end
    )
   end
  )
  +
  (
   select isnull(sizetemp1.sum1, 0) + isnull(sizetemp2.sum2, 0) + isnull(sizetemp3.sum3, 0) 
   from
   (
    select sum(cast(img_size as bigint)) + sum(cast(txt_size as bigint)) + sum(cast(loc_size as bigint)) as sum1 from doc 
    where pagenum >= 0 and toc.tocid = doc.tocid
   ) sizetemp1,
   (
    select sum(cast(altedoc_size as bigint)) as sum2 from altedoc where altedoc.tocid = toc.tocid
   ) sizetemp2, 
   (
    select isnull(sum(cast(datalength(ann.ann_text) as bigint)), 0) + isnull(sum(cast(ann.attach_size as bigint)),0) + 
    isnull(sum(cast(datalength(ann.bitmap) as bigint)), 0) as sum3
    from ann
    where ann.page_id in 
    (
     select page_id from doc where doc.tocid = toc.tocid and doc.pagenum >= 0
    )
   ) sizetemp3
  )
 ) as total_recycled_entry_size
 from toc
 left join recycle_bin on toc.tocid=recycle_bin.tocid
 where toc_flags & 2048 = 2048) recycle_bin_sizes

 

This subquery returns the individual sizes of each recycled entry:

 

 select (case when recycle_bin.orig_name is null then toc.name else recycle_bin.orig_name end) as name, toc.tocid,
 (
  (
   case when toc.edoc_storeid is null
   then 0
   else
    (
    case when toc.edoc_size is null
    then 0
    else toc.edoc_size
    end
    )
   end
  )
  +
  (
   select isnull(sizetemp1.sum1, 0) + isnull(sizetemp2.sum2, 0) + isnull(sizetemp3.sum3, 0) 
   from
   (
    select sum(cast(img_size as bigint)) + sum(cast(txt_size as bigint)) + sum(cast(loc_size as bigint)) as sum1 from doc 
    where pagenum >= 0 and toc.tocid = doc.tocid
   ) sizetemp1,
   (
    select sum(cast(altedoc_size as bigint)) as sum2 from altedoc where altedoc.tocid = toc.tocid
   ) sizetemp2, 
   (
    select isnull(sum(cast(datalength(ann.ann_text) as bigint)), 0) + isnull(sum(cast(ann.attach_size as bigint)),0) + 
    isnull(sum(cast(datalength(ann.bitmap) as bigint)), 0) as sum3
    from ann
    where ann.page_id in 
    (
     select page_id from doc where doc.tocid = toc.tocid and doc.pagenum >= 0
    )
   ) sizetemp3
  )
 ) as total_recycled_entry_size
 from toc
 left join recycle_bin on toc.tocid=recycle_bin.tocid
 where toc_flags & 2048 = 2048

Note: this doesn't consider recycled pages.

 

To get the size of recycled pages (i.e. pages that were recycled individually, not pages that belong to a recycled document), run the following queries.

 

Total size of image/text/location/thumbnails:

 

select sum(cast(img_size as bigint)) + sum(cast(txt_size as bigint)) + sum(cast(loc_size as bigint)) from doc where pagenum < 0

 

Total size of all annotations:

 

select isnull(sum(cast(datalength(ann.ann_text) as bigint)), 0) + isnull(sum(cast(ann.attach_size as bigint)),0) + 
isnull(sum(cast(datalength(ann.bitmap) as bigint)), 0)
from ann
where ann.page_id in 
(
 select page_id from doc where doc.pagenum < 0
)

 

1 0
replied on May 28, 2014

Cool. I'm always happy to get within the ballpark, but your stuff is pretty accurate. I always forget about edocs since we don't use them very often.

 

Thanks!

0 0
replied on May 28, 2014

Thanks. Will this be added in the future release as a function of either the Admin Console or the Client?

0 0
replied on May 28, 2014

I don't know of any plans for this but we will consider it for a future release.

0 0
replied on April 13, 2015

This is great.

Is the result in bytes?

 

Thanks

0 0

Replies

replied on May 28, 2014

I have also wondered the same thing. I have always thought that maybe it's something you can find by going through the database tables and finding the entries in the recycling bin and then looking them each up to find the filesize of each. 

 

Can a Laserfiche Employee with more familiarity of the databases comment on whether or not this is how we would go about accomplishing this goal?

0 0
replied on May 28, 2014 Show version history

This is a quick C# script that I hacked together in LINQPad (IMO the best tool for "unsupported" excursions into the Laserfiche database).

 

EDIT: Here's a cleaner way to do it, and then the equivalent SQL.

//C#
var recycled = from r in Recycle_bins
      from d in Docs.Where (d => d.Tocid == r.Tocid).DefaultIfEmpty()
      where r.Toc.Pagecount > 0
      select d;

decimal size = recycled.Sum (r => r.Img_size + r.Lft_size + r.Loc_size + r.Txt_size);

(size / 1024 / 1024).ToString("F2").Dump();
--SQL
SELECT (SUM(doc.img_size + doc.lft_size + doc.loc_size + doc.txt_size) / 1024.0 / 1024.0) AS Total
FROM recycle_bin
LEFT OUTER JOIN doc ON doc.tocid = recycle_bin.tocid
INNER JOIN toc ON toc.tocid = recycle_bin.tocid
WHERE toc.pagecount > 0
0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.