how to temporarily disable transaction log

Posted on 2003-03-19
Medium Priority
Last Modified: 2009-09-08
Hi there!  Is it possible to disable transaction logging for a particular query?  I have a couple stored procs that do some inserts/deletes from some temp tables that aren't too important - but their transactions still add to our transaction log, which occasionally fills our disk space up.  Is there a command I can call at the top of each stored proc to disable any transactions temporarily for those few queries?

Thanks for any help!
Question by:mallio
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

Expert Comment

ID: 8167967
IF MS SQL Server, try the "Dbcc shrinkfile" command with the truncateonly option.  See BOL for syntax.  (Search for dbcc shrinkfile).

Expert Comment

ID: 8167970
It won't disable the log files, but it will empty them for you.

Accepted Solution

gletiecq earned 100 total points
ID: 8168994
In general, the design concept of a transaction log would specifically prevent this kind of operation.  If a primary purpose of a transaction log is to allow the restoration of a database to a point after the last backup of a database file, it's imperative that all data operations be fully logged.  If another primary purpose of a transaction log is to provide for rolling back uncommitted operations, it's imperative to fully log all modifications.  Now often there are ways around this that may be appropriate to certain situations, but managing transaction log size would certainly NOT be one of these situations.

Many, if not all DBMS's allow for dumping a transaction log either periodically or when some condition is reached as a way to limit size.  Most SQL extentions allow dumping a tran log prior to running an operation to prevent it from filling up.  There are a lot of other issues that are of interest here.

If you have particularly long transactions, transaction log dumps and truncations perform poorly, as you can't easily dump a log containing active transactions, and you end up truncating very little if anything at all until the open transactions are cleared.  Using a delete/insert instead of an update to modify data causes unnecessary transaction log entries.  There are other less common issues as well, and a bunch of performance and tuning stuff that can impact trans log growth rates and where they occurr.  And of course, there's the ever present issue of management practices.

The issue here is most likely not "how to I break the trans log" but "how to I efficiently do X" where "X" is the operation that is causing you to fill the transaction log.

I hope this helps.

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.


Assisted Solution

rherguth earned 100 total points
ID: 8170476
Depending upon the logging the DBMS is configured for, you may or may not be logging bulk inserts.  Through configuration, you should be able to avoid logging bulk inserts.  It depends on your circumstances whether this is an advisable option for you.  Most bulk insert facilities are designed to work from a file to a table, rather than from one table to another.  SELECT INTO being an exception.

I would agree with all of gletiecq's comments and just add for clarification that the delete/insert log operation used for updates is for increased performance.  Changing that to an update operation through a DBMS configuration or connection configuration may save log space, but at some performance cost.

Breaking up INSERTs and DELETEs into smaller batches will help speed up the time the DBMS takes to dump the log.  It sometimes helps speed up the transactions as well if any lock contention/escalation is ocurring.  Breaking up the INSERTs may just mean modifying a WHERE clause so as not to catch all the rows in the first batch.
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 8195787
Combining all of the above:
NO, you cannot do this in 1 database
YES, you can do this in 2 databases

In fact, if you have some tables that are not important or can easily be restored, create them in a separate database which you put in NOLOG mode or where the logging mode is configured as you don't matter the transaction log. This depends very much on the RDMBS you use...
The other tables stay in your primary database.


Expert Comment

ID: 10914786
recommend split:  gletiecq and rherguth

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this article, we’ll look at how to deploy ProxySQL.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

752 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