Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2002
    Location
    Sweden
    Posts
    3

    Question Unanswered: SQL Server 2000 - How to turn off trans. log?

    Hi everyone,

    I have a simple question: How do I turn off the transaction logging?

    I'm performing excessive bulk copying several times and don't want any logs. I haven't found any post on dbforum, but forgive me if the question already have been answered.

  2. #2
    Join Date
    Jul 2002
    Posts
    229
    Set the recovery mode to Bulk-Logged.

    (Properties of database, Options tab in Enterprise Mgr)

  3. #3
    Join Date
    Nov 2002
    Location
    Sweden
    Posts
    3
    Originally posted by Coolberg
    Set the recovery mode to Bulk-Logged.

    (Properties of database, Options tab in Enterprise Mgr)
    That doesn't turn the log entirely off, does it? I believe I've tried that and the log still grows.

    The thing is that this is a performance issue realy. We are trying to write large amounts of data to our tables in SQL Server and this happens in intervals of 5 sec. It's a single client application and we try to use the bcp utility with the 'xp_cmdshell' stored procedure. I've been tweaking on this for a week now and I think the logs are the bottle-neck.

    Any suggestions?

  4. #4
    Join Date
    Jul 2002
    Posts
    229
    True, does not turn logging off, don't know if it's possible to turn it off.
    Maybe you could reduce the size by backing up the logg and shrinking?
    Of course, that would be a big performance issue too...

  5. #5
    Join Date
    Nov 2002
    Location
    Sweden
    Posts
    3
    My quest goes on then.

    Thanks anyway. Great forum.

  6. #6
    Join Date
    Nov 2002
    Posts
    4

    shrinking log

    Do you need to preserve your transaction logs? You can set your recovery model to 'simple', which releases your transaction log. All used space becomes reserved. Then shrink your transaction log, take a full database backup, re-set your recovery model to the desired model and take another full database backup. This is a fairly long process if your database is large, but ensures you won't lose any data as long as you don't delay between the steps.

    Good luck!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •