How do you stop/start transaction file logging ??

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

Who is Participating?
leotohillConnect With a Mentor Commented:
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.

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....


manney_mcvickerAuthor Commented:
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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.
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.
Ariel GarciaJefe de SistemasCommented:
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...!
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.

Ariel GarciaJefe de SistemasCommented:
Sorry, but the switch was located on help information and work whell to me....!
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."

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
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
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.

All Courses

From novice to tech pro — start learning today.