?
Solved

Problems with Notes database size

Posted on 2006-04-24
32
Medium Priority
?
407 Views
Last Modified: 2013-12-18
Hi all,

I have a strange problem...

I have a database containing approx. 1800 documents that is nearly 400mb in size.
There are no attachments in the documents and no large images used in the database design.

I do have agents that delete all records and create new records every 8 hours.
However, I have disabled soft deletions and would expect this database to be no more than 60mb in size.
I have also tried a compact without success. Are these deleted documents somehow still being stored in the database?

Or, is there something I'm missing?
0
Comment
Question by:shuboarder
  • 13
  • 11
  • 4
  • +2
32 Comments
 
LVL 43

Expert Comment

by:Steve Knight
ID: 16523310
Have you tried a compact -B  from the server to reduce file size?

load compact path\filename.nsf -B

or possible -C to do an old Copy style compact.

Deleted documents should just leave a stub behind but until you run a compact the unused space will remain in the database.

Steve
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 16523320
If you delete all records using your script and then check the database properties for no. of documents does it show 0 or could it be there are other documents in there that do not meet your view selection formulae?

It would also be worth compacting the db as above when there are no. documents in it then let your script re-populate.

Is there no way your script could update the existing records BTW rather than re-create them three times a day?

Steve
0
 
LVL 22

Expert Comment

by:mbonaci
ID: 16525415
Hi shuboarder,
if you've already analyzed the db design and established that design isn't causing this size inconsistency and Compact doesn't yield anything I suggest you to create the view with selection formula:
    @All
Add the column with formula:
    Form
Add the column with the formula:
    @DocLength/1024
(@DocLength returns the approximate size of a document in bytes, so this way you'll get kilobytes)

Make all columns sortable desc. and asc. and you'll be able to analyze the docs size.

Hope this helps,
Marko
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 46

Accepted Solution

by:
Sjef Bosman earned 800 total points
ID: 16526001
It also depends on the complexity of the views you have. Multi-level categorized views can be huge.

About deletion stubs: the time they will linger in your database depends on the Replication settings in the database; look under Space Savers, "Remove documents not modified...", even when is not selected that number will be used; those stubs will remain a third of the number of days that is mentioned, so if it says 90 days, stubs will stay for 30 days at least.

Delete ALL records?? I suppose you can do better than that...

5400 stubs per day, that's 162,000 per month. Hmm...

Check with NotesPeek how many stubs there are.
0
 
LVL 46

Assisted Solution

by:Sjef Bosman
Sjef Bosman earned 800 total points
ID: 16526013
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16526056
So if your database is NEVER replicated, you can bring down the value for "Removing documents" to some decent value, e.g. 3. As you say, all documents are re-created every day...
0
 
LVL 21

Author Comment

by:shuboarder
ID: 16526150
Hi Sjef,

thanks for the reply...

Where can I set this?
0
 
LVL 21

Author Comment

by:shuboarder
ID: 16526249
Found it....

Database >> Replication >> Settings >> Space Savers

I have set this to 3 as suggested.

Lets see what happens. (Don't worry, this is only a test database at the moment!)
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16526457
After having set it to 3, compact the database once again.
0
 
LVL 43

Assisted Solution

by:Steve Knight
Steve Knight earned 200 total points
ID: 16528895
If you set it to 0 BTW and then re-open the database it will clear all the stubs immediately...

http://hostit1.connectria.com/twduff/home.nsf/plinks/TDUF-5YN56F

Steve
0
 
LVL 14

Expert Comment

by:cezarF
ID: 16531386
just a thought.

any special reason why you have to delete all docs and create new ones again? would updating the existing docs be easier?
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16532204
To put it all into perspective: 400 Mb on an 80 Gb disk that costs $80... The file storage costs you half a dollarcent!
0
 
LVL 21

Author Comment

by:shuboarder
ID: 16532372
Sjef,

it's still 330mb big after setting to 3 and compacting

Dragon-IT

I don't appear to be able to set this to 0. 1 is the lowest.

CezarF

Updating existing docs would be better yes, but this way seemed easier.

Sjef,

it's not the cost of storage that is the problem. It's going to be the cost of replicating nearly 400mb of data across our WAN. If these deletion stubs exist in the quantity you suggest, then replication of this database will eventually grind to a halt.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16532480
So you ARE replicating the database! Then the setting to 3 won't be correct, for if replication doesn't work for just one day, you'll see documents in your views that should have been deleted.

If replication is all you are worried about, then relax. Those 400 Mb aren't copied from one replica to the other, but only the differences. What will happen during a replication is something like this:
- database documents are compared (only ids and update times)
- you deleted 1800 documents, so 1800 deletions are sent to the other replica
- you added 1800 documents, so all documents are replicated to the replica database
This will happen no matter the physical size of the database. Even the replicas may differ in physical size, depending on the views that are used. Unused views cost nothing.

To see the actual sizes of the views in your database, please look in the log.nsf database on the server, open Usage/by Size, locate the document for your database and open it...
0
 
LVL 21

Author Comment

by:shuboarder
ID: 16532510
Thanks for the info. Sjef.

However, if someone is to make a local replica, this takes a long time.
Does this form of replication (i.e. new replica) replicate all these stubs as well?

If possible I would like to lose as many of these stubs as possible.

Are there any replication settings I can change to improve this and reduce the size?
0
 
LVL 14

Expert Comment

by:cezarF
ID: 16532516
shuboarder, would you rather do extra coding than to face db size problem?  :)

by deleting all docs and recreating them, notes will update all view indices, ft index, etc, and who knows what else domino has to update internally?
0
 
LVL 21

Author Comment

by:shuboarder
ID: 16532556
Cezar, the difficult thing is, these records are created from MS Excel.

I don't know how much code would be involved in querying Excel and synching it with a Notes database.
It seemed easier to just have 1 agent import all excel data. Then a second agent to delete the contents of the view periodically.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16532629
As I said before, you can do batter than just delete all records. I fully agree with Cesar.

Just an alternative way:
- create your main and a temporary database (with the same design)
- import Excel in the temp. database
- create an agent that
    opens two views at the same time, in both databases
    compares documents, finds differences and updates the existing documents,
    adds documents if they don't exist yet
    removes documents that no longer exist
- the temp. database need never be replicated
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16532635
batter -> better  

:$ Xcuse me...
0
 
LVL 14

Expert Comment

by:cezarF
ID: 16532644
shuboarder, i see. i guess you'll have to bear with the trade-offs then. :)
0
 
LVL 14

Expert Comment

by:cezarF
ID: 16532674
i would do it this way.

an agent to
 - flag all docs for deletion
 - read excel file to check if doc exists and update accordignly (and set the delete flag to false)
 - delete all docs flagged for deletion.

0
 
LVL 21

Author Comment

by:shuboarder
ID: 16532689
Sjef, although that sounds like a great idea, I get the feeling that it's not that sraight forward...
I admit the database situation is not ideal, but is there anything else I can do to improve it?
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16533348
If the documents are never modified manually, the process can really be pretty straightforward.

You already had a look in log.nsf? Usage/By Size? What's so large in your database?
And NotesPeek? What does it say? Or ScanEZ? Or DXLPeek from OpenNTF? To download:
    http://www-10.lotus.com/ldd/sandbox.nsf/0/2791869F4E1D3FA385256F2C00432973?Open
    http://www.ytria.com/WebSite.nsf/Er_Download?ReadForm&Lang=en
    http://www.openntf.org/projects/pmt.nsf/ProjectLookup/DXLPeek

I have a database here that is 720 Mb with 62,000 documents. Not many deletions (10%) so I guess 400 Mb can hardly be called "slim". Some ideas to reduce space:
- select "Don't maintain unread marks" in the Advanced Database Properties
- disable specialized response hierarchy information (and run a Compact)
0
 
LVL 21

Author Comment

by:shuboarder
ID: 16533625
Ok,

NotesPeek didn't work....

ScanEZ says I have 21992 deletion stubs on one replica
and 7344 on another.





0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16533867
That must be after setting the number of days to 3 and the Compact. To be on the safe side, set that number to 21, to cope with accidentally delayed replication of a week.

And what says log.nsf?
0
 
LVL 21

Author Comment

by:shuboarder
ID: 16584101
Hi Sjef,

I have set this to 21 and have let it settle for a couple of days. However, one replica is 32MB and the other is 48MB and growing in size daily. Is this normal? and why is only one replica growing?

Unfortunately I can't access the log.nsf so can't comment on that.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16584221
Can't access log.nsf?? That would be the first thing to do, either ask for an axtract of it from one of the Admins, or go to them and ask them about your database, from the view Usage/By Size. I think it's ESSENTIAL you get that information, otherwise we'll just continue to stumble in the dark.

If one replica is growing faster than the other, then probably one replica is used and the other isn't. A database in use will mean that views are opened. Unopened view occupy (almost) no space, that's why it takes some time to open a view no one opened before (in the last 45 days).
0
 
LVL 21

Author Comment

by:shuboarder
ID: 16631249
The database seems to be settling down now.
However, when I try to create a new replica it is taking hours.

I assume it is trying to also replicate all the deletion stubs?
Is there any way to prevent replication of stubs or would that defeat the object?
0
 
LVL 21

Author Comment

by:shuboarder
ID: 16631529
Arrgghhhh there are 1.5 million updates!
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16631908
But of course it will replicate deletion stubs! That's the whole point of those things, that every database knows that a certain document should be deleted if it still exists.

Admin, with log.nsf??
0
 
LVL 21

Author Comment

by:shuboarder
ID: 16631946
So is the answer just don't ever replicate it?

What do I need to look for in log.nsf?
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16634331
To see the actual sizes of the views in your database, please look in the log.nsf database on the server, open Usage/by Size, locate the document for your database and open it...

Ask any decent Admin to assist you if you don't have access yourself. They ought to help you. I 'spose...
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For Desktop Techs: How to retain a user's Notes configuration data when swapping out the end user's computer. (Assuming that you are not upgrading to a completely different version of Notes client) All you need to do is: 1) install Notes o…
This is an old article, please see an updated version of this article, located here: http://www.experts-exchange.com/articles/23619/Notes-8-5x-Windows-7-Notes-info-and-tips.html
Screencast - Getting to Know the Pipeline
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question