• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 378
  • Last Modified:

Unlogged Transactions

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
chonsp
Asked:
chonsp
1 Solution
 
rferlyCommented:
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
 
chonspAuthor Commented:
That would work.  But I think a more generic set rowcount and loop through the deletion process is better.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now