How do you stop/start transaction file logging ??

Posted on 2001-07-13
Last Modified: 2007-12-19
I am using ASA7.02

I need to stop transaction logging within a stored procedure as i need to delete several thousand records from two lookup tables and then regenerate the data.

STOP LOGGING and START LOGGING commands only seem to be valid within I-SQL

Question by:manney_mcvicker
  • 3
  • 2
  • 2
  • +4

Expert Comment

ID: 6280736
3 possiblilities:

- truncate table. This is not logged but has the disadvantage that you can not specify a where clause.
- drop table/create table. No deletes logged, only the drop table. However also you can not specify which records you want to delete and which to keep.
- create a loop. Either use a cursor or a min/max loop and delete 10.000 or so rows each loop. This is the safest as you can specify which rows to delete, you may need to clear your log after each loop.

Hope this helps....



Author Comment

ID: 6280910
The reason I wanted to stop logging is from a performance point of view.

Because the data is regenerated i am not that concerned that the log file is updated every time a record is deleted/inserted/updated and every time i regenerate the data the log file is increasing by quit a substantial amount

is there no way for switching logging off though a TEMPORARY OPTION setting like you can in MS-SQL

Expert Comment

ID: 6281094
In Sybase ASE there's the option 'trunc log on checkpoint' however that won't help if you delete all rows in one batch... then you still need one of the mentioned options.
And I'm not sure if it works in ASA too.

Expert Comment

ID: 6294793
Sybase ASA does not use options the same ASE.Syabse ASA and ASE are not as closely related as Say Oracle Server Enterprise and Personal Oralce.

There is a startup parameter -m which will truncate the log after a transaction is commited.
I dont think its possible to turn transaction logging off at will, Which would make sense from a data integrity point of view.i.e You either recover all the transactions or none at all.

You could create the database without a transaction log .However besides losing the obvious protection against media corruption there will be performance issues as there will be a checkpoint to disk after every transaction is committed.
Sybase recommend logging due to the performance gains it gives.

Accepted Solution

leotohill earned 50 total points
ID: 6419979
ASA does not allow you to turn off logging for specific operations.  Logging behavior can only be changed during a database shutdown.

I agree with the other advice given:

- truncate table or drop/create will generate very little log traffic.

- use of a log improves COMMIT performance drastically, and doesn't hurt performance much for other operations.

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.


Expert Comment

by:Ariel Garcia
ID: 6466348
Of course you can turn off the transaction with this modify command line option

Force the database to start without a transaction log

I hope enjoy...!

Expert Comment

ID: 6471063
leirags, -f does NOT allow you to run the db without a tran log.   The -f switch forces the db to start without a transaction log in a recovery situation.  It starts the db, completes the recovery (if possible) and immediately shuts down again.  

If you want to run without logging, you can start the engine with the -b (bulk operations) options.


Expert Comment

by:Ariel Garcia
ID: 6472779
Sorry, but the switch was located on help information and work whell to me....!

Expert Comment

ID: 6472924
From the ASA7 help:

"If there is a transaction log in the same directory as the database, the database server carries out a checkpoint recovery, and a recovery using the transaction log, and then terminates?it does not continue to run. You can then restart the database server without the -f option for normal operation."


Expert Comment

ID: 7430470
Dear: leirags

I've rejected your proposed answer as Experts Exchange holds an experiment to work without the answer button.

Paragraph: Site Update for Wednesday, November 06, 2002

By this rejection the Asker will be notified by mail and hopefully he will take his responsibility to finalize the question or post an additional comment.
The Asker sees a button beside every post which says "Accept This Comment As Answer" (including rejected answers) -- so if he/she thinks yours is the best, you'll be awarded the points and the grade.

Thanks !


Community Support Moderator
Experts Exchange

Expert Comment

ID: 9882608
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: leotohill

Please leave any comments here within the next seven days.

EE Cleanup Volunteer

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
My first ASE 15.7 developer edition installation. 12 2,080
SQL Query Syntax 11 161
Time optimization for insert/update in ultralite. 3 126
SQL Query Help! 11 116
Cloud-based technologies and services will continue to grow in popularity in 2017 thanks to the simple, scalable and cost-effective solutions they deliver. Here are three areas where cloud adoption is poised to really take off.
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

863 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now