Solved

Unlogged Transactions

Posted on 1998-07-02
2
373 Views
Last Modified: 2008-03-17
The transaction log in our sybase 11 production database fills up fast
although its size is fairly large, resulting is a half-minute pause at
an unpredictable time while the log is dumped.

Under normal operation, one process deletes a large set of rows from a
table and then inserts another set.  Other processs read these rows.
I suspect the inserts and deletes are logged and cause the problem.
There is no transaction involved and we do not want to recover that
particular table if the database goes down.

Truncate table (which does not log) is not allowed in our production databases.  
Any other suggestions for getting around this problem?
0
Comment
Question by:chonsp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 

Accepted Solution

by:
rferly earned 100 total points
ID: 1098136
even when you do not explicitly use the begin tran and commit tran, the database engine uses the transaction log.
I suppose that you issued a command like
delete mytable
(maybe with a where clause)

the important point is that from the beginning of the delete up to the end every deleted row is logged so that is the system crashes , sybase can make a recovery and start normally
Your problem is that the transaction log will be full before the end of the delete
So just try this
Make an estmiation of the number a rows you want to delete, say for exemple 100000
then try to issue your delete in isql
with a where clause
delete mytable where sex="M"
dump tran with no log
then delete mytable where sex ="F"

if it doesnot work try a more selective where clause like
delete mytable where state="CA"
dump tran with no log
delete mytable where state="NY"
dump tran with no log
...

The important thing is that you must decrease the number of rows delete at the same time



0
 

Author Comment

by:chonsp
ID: 1098137
That would work.  But I think a more generic set rowcount and loop through the deletion process is better.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
We asked our MSP customer base what their favorite tools were and how they help them serve clients. We focused our questions on favorite tools in the following categories: >PSA tools >RMM tools >Alert management tools >Communication tools and Mo…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

751 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