Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2007
    Posts
    3

    Question Unanswered: Transaction Log Dump never ends

    I am having difficulties with performing a transaction log dump - all of a sudden, it does not end. It has litterally run for over a day, not ending. When I do a no_log, and then truncate_log, the dump works. But after the next db dump, it will run forever again.

    Ideas?

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    How big and full is your t log getting? perhaps you should back it up more often. perhaps the application doing something causing excessive logging.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Nov 2007
    Posts
    3
    Today it worked as it should. But - the T-Log is 60GB, and has grown to 90. It is a very busy DB, a primary DB for a grocery food chain. This happened all of a sudden, and nothing changed.

    Yesterday, to initialize the T-Log after the full backup took 7 hrs to dump 6GB of data from the T-Log. A T-Log dump is performed every 15 minutes on this particular DB.

    Databases are located on iSCSI SAN's as well as the dump devices. Full backups are performed daily using Red-Gate Software's SQL Backup, T-Log dumps are performed using standard SQL dumps.

    This issue has occured periodically before, yet I have been unable to find a reason for it. BTW tempdb is located on seperate internal drives, and not the SAN.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Like i said. increase frequency of you t log backups.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Nov 2007
    Posts
    3

    Thumbs down Interesting thought -

    unfortunately, by now you must realize that everything is not as black and white as one may hope. Increasing T-Log dumps in this case is not going to accomplish anything. The average T-Log utilization between T-Log dumps of a 60GB T-Log is under 3%.

    BTW - just for the record, all of the obvious has been checked, disk contention, IO, CPU, memory, etc. Been doing this too long to avoid the obvious. Let's think outside the box.

  6. #6
    Join Date
    Aug 2008
    Location
    India
    Posts
    55
    Share with us the DB size , recovery model of db , Is there any bulk inserts done or you r trying to take log backup while its used , put sp_helpdb for the db and check autogrowth option set either in MB or % , if its in % bring it down to MB

Posting Permissions

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