Solved

How do you stop/start transaction file logging ??

Posted on 2001-07-13
12
575 Views
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


Anyone?
0
Comment
Question by:manney_mcvicker
[X]
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
  • 3
  • 2
  • 2
  • +4
12 Comments
 
LVL 3

Expert Comment

by:ahoor
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....

Arjan




0
 

Author Comment

by:manney_mcvicker
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
0
 
LVL 3

Expert Comment

by:ahoor
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.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Expert Comment

by:smccartn
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.
0
 
LVL 2

Accepted Solution

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

0
 
LVL 2

Expert Comment

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

-f
Force the database to start without a transaction log


I hope enjoy...!
0
 
LVL 2

Expert Comment

by:leotohill
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.

0
 
LVL 2

Expert Comment

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

Expert Comment

by:leotohill
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."

0
 

Expert Comment

by:modulo
ID: 7430470
Dear: leirags

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

See:        http://www.experts-exchange.com/jsp/communityNews.jsp
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 !

modulo

Community Support Moderator
Experts Exchange
0
 
LVL 6

Expert Comment

by:ChrisKing
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.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

ChrisKing
EE Cleanup Volunteer
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

While it may be true that the internet is a place of possibilities, it is also a hostile environment lurking with many dangers. By clicking on the wrong link, trusting the wrong person or using a weak password, you are virtually inviting hackers to …
Building a cohesive image for your brand is vital to making an impression on consumers. When the economy is tough, brands do better than unbranded  products. This can have a huge impact on your long-term profits, as the economy goes up and down.
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

733 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