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
Solved

Unlogged Transactions

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

Suggested Solutions

Title # Comments Views Activity
Small DB [ Application with 20GB Storage Size Minimum ] 4 517
Sybase Customized sp_thresholdaction 2 683
SYBASE ASE HA Configuration 8 407
Default Read Only User Sybase DB 1 142
Giving access to ESXi shell console is always an issue for IT departments to other Teams, or Projects. We need to find a way so that teams can use ESXTOP for their POCs, or tests without giving them the access to ESXi host shell console with a root …
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

840 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