Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 543
  • Last Modified:

Turn off Transaction Logging in SQL 2000


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
MacRena
Asked:
MacRena
  • 3
  • 2
  • 2
  • +2
1 Solution
 
TheSpiritCommented:
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
 
pdebettaCommented:
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
 
solartCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
MacRenaAuthor Commented:

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
 
solartCommented:
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
 
David ToddSenior DBACommented:
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
 
MacRenaAuthor Commented:

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
 
David ToddSenior DBACommented:
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
 
MacRenaAuthor Commented:

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now