Solved

Unlogged Transactions

Posted on 1998-07-02
2
372 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
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Read our guide on how to survive being on-call.
Although a lot of people devote their energy toward marketing for specific industries, there are some basic principles that can be applied to any sector imaginable. We’ll look at four steps to take and examine how those steps were put into action fo…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

756 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