Solved

Unlogged Transactions

Posted on 1998-07-02
2
370 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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

This tutorial shows how to create a greeting card by combining two image layers and a text layer on a PC using a free image editing app.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
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 …

770 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