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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.


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

Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

696 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