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!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.