Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    Minnesota
    Posts
    2

    Unhappy Unanswered: Cannot truncate transaction log.

    DB Newbie question ahead.

    I have a mere 100MB db with a 4GB transaction log. I want to truncate the log as I understand that truncating it will shrink the log by removing the transactions that have already taken place. However, the option to do a transaction backup is greyed out. I suspect this is from the db being in transactional replication with another server; however, I don't know for sure.

    Are there any other ways that I can shrink the transaction log? I would like to do shrink it without taking the db offline either.

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: Cannot truncate transaction log.

    I think the recovery model was set to simple, that's why you can't take log backup. You can use DBCC Shrinkfile to shrink your log file to the size you want.

  3. #3
    Join Date
    Feb 2004
    Location
    Rhode Island
    Posts
    69
    Are you using SQL 2000?
    If so you can do that by the following

    - Go to DB properties and change the backup type to FULL
    - Take a backup of the database and keep it safe.
    - Go to DB properties again change the backup type to SIMPLE
    - Go to all tasks and Shrink the file
    - Go to DB properties again and change the backup type to FULL again so that it logs everything.

  4. #4
    Join Date
    Feb 2004
    Location
    Minnesota
    Posts
    2
    Yes, I am using SQL2000. That's important information
    SQL2000 on win2003.

    smasanam: I tried that and it didn't shrink the log file. Still 4.5GB.

    Is the log file important for transactional replication? This db is replicated nightly to another server. If everything is updated and fully bkup'd would the log file be necessary? Or could I just delete it?

    Thanks for the help.

Posting Permissions

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