Results 1 to 5 of 5

Thread: Log Files!?!?

  1. #1
    Join Date
    Jun 2003
    Location
    Montreal
    Posts
    38

    Question Unanswered: Log Files!?!?

    Hi,

    I'm running a SQL Server used only for development and testing. Because of this, a lot of DELETE commands (and other "logable" operations) are issued.

    Now, I DON'T WANT to use any logging at all on this server because I don't see the use for it and it's taking too much space on my hard drive.

    How can I remove the log files and stop SQL Server from using them? Plus, maybe for some reason that I don't understand, this is not such a good idea. If so, can you please tell me why?

    Thanks,

    Skip.

  2. #2
    Join Date
    Jan 2004
    Location
    Boston
    Posts
    58
    I'm not aware of a db that is operating normally without a log.

    first of all, delete command is logged. If you look for non-logged operations, please check BOL for "bulk insert", "select into", "truncate table" etc. They will not cause the log to grow.

    But a lot of times, you have to use "DELETE". you can turn the database into simple recovery mode 'cause it is testing server. The log will be truncated.

    You can also run scheduled job to do "backup log xx with truncate_only" with proper frequency.

    When none of the above works, you need to look into if you have open transactions by DBCC OPENTRAN. You should also modify your delete statement to do transactions at a smaller scale, say, commit transaction every 100 rows. It will allow the log to be checkpointed and truncated.

  3. #3
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Set the Recovery model of database to "Simple"
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  4. #4
    Join Date
    Jun 2003
    Location
    Montreal
    Posts
    38
    Alright, now that's nice!

    Still though, my transaction log is 602 Megs, I find it a little too big. I'd like to backup it because, as I understand, it's the only way to reduce it.

    Now, when I go to the backup screen, I can't choose to backup my transaction log because the option is disabled. Why is that?

    Thanks again,

    Skip.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    In the database properties window navigate to Transaction Log tab and see how much is allocated. I suspect that 602 is the number you're going to see. You'll probably need to do shrinkfile against your trx log. The reason Backup Transaction Log option is disabled is because your database is in Simple Recovery mode.

Posting Permissions

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