Solved

Turn off Transaction Logging in SQL 2000

Posted on 2002-07-18
9
527 Views
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.

http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=msaccess&qid=20325080

Thanks.
0
Comment
Question by:MacRena
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 4

Expert Comment

by:TheSpirit
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)
0
 

Expert Comment

by:pdebetta
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.

Later,

--Peter
0
 
LVL 1

Accepted Solution

by:
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

solart
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 4

Author Comment

by:MacRena
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???)


0
 
LVL 1

Expert Comment

by:solart
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
0
 
LVL 35

Expert Comment

by:David Todd
ID: 7168681
Hi,

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.

Regards
  David
0
 
LVL 4

Author Comment

by:MacRena
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!
mac
0
 
LVL 35

Expert Comment

by:David Todd
ID: 7183940
Hi,

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.

Regards
  David
0
 
LVL 4

Author Comment

by:MacRena
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.
 

0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Slow Connectivity over ODBC 8 32
Attaching Database Failed ? 3 37
SqlServer no dupes 25 34
Update a text value in another table 10 35
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

815 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

13 Experts available now in Live!

Get 1:1 Help Now