Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 10981
  • Last Modified:

how to temporarily disable transaction log

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!
2 Solutions
IF MS SQL Server, try the "Dbcc shrinkfile" command with the truncateonly option.  See BOL for syntax.  (Search for dbcc shrinkfile).
It won't disable the log files, but it will empty them for you.
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.

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

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.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.

recommend split:  gletiecq and rherguth

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now