Turn off Transaction Logging in SQL 2000

Posted on 2002-07-18
Last Modified: 2012-05-04

How can I turn off Transaction Logging in SQL 2000?

I am importing data through Access 2000 into linked SQL tables and don't want to spend time or resources to log the updates.

Question by:MacRena
  • 3
  • 2
  • 2
  • +2

Expert Comment

ID: 7163413
Everything (insert/update/delete) goes via the transaction log....and there is no way to 'turn it off'....just not possible. What you can do is to limit how much goes into the log and that is by setting the recovery model to simple for the database you are importing into. This will allow the transaction log to be cleared down to its last checkpoint (or oldest un-replicated transactio which ever is oldest)

Expert Comment

ID: 7164209
BCP and 'Select Into' can be performed with minimal logging if certain criteria is met. Fast BCP requires no indexes on the table (including primary and unique keys), along with some other criteria to allow it to worked in a non-logged mode. You also have to turn on the 'select into/bulkcopy' db option.

[TheSpirit stated that] You need to be using the 'Bulk-Logged Recovery' model for that database as well (please read up on this before switching - it is less fault tolerant than the other recovery models).

You can look up all this stuff in SQL BOL (Books OnLine).

Now, if you can export the data from Access to delimited files, and then import it using Fast BCP, you will minimize logging

If this is a one time deal, this will do the job, but if this is a repetitive task, it's probably more hassle than it's worth.

'Select Into' is also a possibility, but you would need to run this from within SQL Server, not from Access.



Accepted Solution

solart earned 50 total points
ID: 7164979
Simply put:
a.  Change your recovery model to "simple"
b.  backup your database
c.  update your database
d.  backup your database, again
e.  Set your recovery model as appropriate for application


Author Comment

ID: 7165058

My Recovery Model was set to Full.  

I set it to Simple

I don't need the Transaction log.  I want it to truncate all the time.

The data that I am importing is old sales data and can be recovered from the original files (it will be a pain to re-import, but it can be done)

I have set the Recovery Model to Simple, but I am too unsure of the right way to truncate the log.  I don't know how to set a checkpoint (don't have experience with stored procedures)

Can I click the 'Delete' button on the 'Transaction Log' tab of the Database Properties?  (if not, then what is that button there for???)

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.


Expert Comment

ID: 7165149
As I understand it, the "Delete" button is for deleting database files.  Caveat, I think is that in order for the delete button to work, the database file(s) must be taken off-line (or not open at this time for use - auto close property selected).

To take a database file off-line, using EM, select database in left pane, then right click database, select all tasks, select "take offline".

After database taken "off-line", I believe you could delete the database file which represents the log.

When the database is brought back on line, I believe it will then create a minimum size log file (which will not grow if the recovery mode/model is set to "simple").

HTHs  solart
LVL 35

Expert Comment

by:David Todd
ID: 7168681

There are any number of solutions for shrinking the log file.

But most of them come back to

backup log with truncate only.

Check this out via BOL.


Author Comment

ID: 7180542

hello all - i did not disappear, but am on a different project.
thank you for patience until i return to the customer's site.

dtodd, the tansaction log is now > 8 GB, and if deleting it will not cause a disaster, then i'll probably do that, and THEN set it to backup and truncate.

thanks again!
LVL 35

Expert Comment

by:David Todd
ID: 7183940

Easiest way to shrink an overly large log file is

a Backup the database
b dettach the database
c rename the log file
d reattach the database when SQL will create a new log file

Obviously you have to be able to take the system off-line to do all this, so it doesn't apply to all systems.


Author Comment

ID: 7236860

Thanks to all who offered help.
I think solart posted the solution first, although, TheSpirit, pdebetta, dtodd, I am very grateful for your input.  I wish Experts-Exchange was like Carobit so I could divide the points.


Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

910 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

21 Experts available now in Live!

Get 1:1 Help Now