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
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
  • 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

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.


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???)


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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

730 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