Solved

How do you stop/start transaction file logging ??

Posted on 2001-07-13
12
573 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
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
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Cannot create ODBC connection over the network 3 1,216
SyBase SQL Query Syntax 11 468
sql statement error sybase 2 49
Converting some Sql server syntax to Sybase Syntax. 8 46
In this article we will learn how to backup a VMware farm using Nakivo Backup & Replication. In this tutorial we will install the software on a Windows 2012 R2 Server.
This article outlines the process to identify and resolve account lockout in an Active Directory environment.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

772 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