Solved

How do you stop/start transaction file logging ??

Posted on 2001-07-13
12
571 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
  • 3
  • 2
  • 2
  • +4
12 Comments
 
LVL 3

Expert Comment

by:ahoor
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Expert Comment

by:smccartn
Comment Utility
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
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 2

Expert Comment

by:Ariel Garcia
Comment Utility
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
Comment Utility
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
Comment Utility
Sorry, but the switch was located on help information and work whell to me....!
0
 
LVL 2

Expert Comment

by:leotohill
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

This article will show you how to create an ISO CD-ROM/DVD-ROM image (*.iso), and MD5 checksum signature, for use with VMware vSphere Hypervisor 6.5 (ESXi 6.5). It's a good idea to compare checksums, because many installations fail because of a corr…
In this article, I will show you HOW TO: Create your first Windows Virtual Machine on a VMware vSphere Hypervisor 6.5 (ESXi 6.5) Host Server, the Windows OS we will install is Windows Server 2016.
This video discusses moving either the default database or any database to a new volume.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

744 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

15 Experts available now in Live!

Get 1:1 Help Now