Full Transaction log

  • maechismo_8514

    SSCoach

    Points: 17510

    Comments posted to this topic are about the item Full Transaction log

  • Tom Walters

    SSC Enthusiast

    Points: 151

    Krishna - Good article. Question though. What if the logfile expanded to fill the rest of the drive. Then what?


    Tom Walters

  • curlywink

    Valued Member

    Points: 60

    What about shrinking the database with replication???

  • jts2013

    Hall of Fame

    Points: 3226

    Hi Krishna

    Very clear article, but I was a bit confused about the first option of shrinking the log - if it is full now, won't shrinkign the physical size mean it is still full afterwards?

    John

  • maechismo_8514

    SSCoach

    Points: 17510

    Tom Walters (3/17/2009)


    Krishna - Good article. Question though. What if the logfile expanded to fill the rest of the drive. Then what?

    hi Tom,

    Thanks for your feedback. if the log file filled the rest of the drive you can think of any one of the steps mentioned in the article.

  • maechismo_8514

    SSCoach

    Points: 17510

    jts_2003 (3/18/2009)


    Hi Krishna

    Very clear article, but I was a bit confused about the first option of shrinking the log - if it is full now, won't shrinkign the physical size mean it is still full afterwards?

    John

    Hello John,

    Thanks for your valuable feedback. When you shrink the log file it removes the inactive VLF's to reuse the space made available.

  • michael_sawyer

    SSC Rookie

    Points: 37

    I cannot believe that backup was the third option. The first and best option is to create proper maintenance on the database. The only transactions you need are those occuring since the last backup. This keeps the back the logs small.

  • maechismo_8514

    SSCoach

    Points: 17510

    michael_sawyer (3/18/2009)


    I cannot believe that backup was the third option. The first and best option is to create proper maintenance on the database. The only transactions you need are those occuring since the last backup. This keeps the back the logs small.

    Hi michael,

    I have pointed out options of what we can do, the preference does not go according to the options mentioned. It totally depends on what you can chose as the best method. Obviously, at the first point if we have a good backup plan, frequent transaction logs, we would definitely not land on this kind of situation.

  • Harald49

    SSC Rookie

    Points: 31

    Hi Krisha,

    in order to shrink the logfile you recommend the command:

    "DBCC SHRINKFILE (transactionloglogicalfilename, TRUNCATEONLY)"

    But BOL for DBCC SHRINKFILE states that:

    "TRUNCATEONLY is applicable only to data files."

    Would you please comment on that.

    Thanks

    Harald

  • saiman li

    SSC Rookie

    Points: 35

    Here is my take on it. (in sql2005, production with recover full mode)

    DBCC LOGINFO('mydatabasename')

    see the status column, you can only shrink the "0" rows after the last 2 or what. Because that was what is "free". (The worse case will be something like replication has a lockup and then the transaction log can't recycle and it will fill to the end)

    If your log hard drive is full. you should buy time and add an extra log file in the other HD.

    do a

    checkpoint

    (that force transactions to write)

    backup your db

    change recover mode to simple that basically free up the log. (not the size)

    Then you can detach the extra log file, shrink the size. turn back to Full mode.

  • Paul Randal

    One Orange Chip

    Points: 29533

    See Kimberly's blog post 8 Steps to better Transaction Log throughput and my blogs

    Search Engine Q&A #1: Running out of transaction log space and

    Search Engine Q&A #23: My transaction log is full - now what?

    Thanks

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • Gail Shaw

    SSC Guru

    Points: 1004484

    Your first suggestion for a full tran log is to shrink it. Maybe I'm missing something, but that's completely the opposite of what's needed.

    The log is full, ie, there is no free space within the log file. Since there is no free space within the log file, a shrink will find no space to release to the OS. Even if the shrink did find some free space, that'll just make the situation worse.

    If the log file is full you need to either reduce the amount of data inside it by either backing the log up or switching to simple recovery, or you need to grow the log file to give it more space.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GNUZEN

    SSC Veteran

    Points: 202

    Can we discuss truncate trans. log in replication environment?

    Here we have publisher database having tran. log with large size even though we have log backup and full backup in place. What is the best practice to truncate trans. log on publisher database?

    I'm looking for step by step instruction here.

    thanks

  • alen teplitsky

    SSC-Dedicated

    Points: 30014

    most of my full log issues have been because of replication. for some reason it didn't truncate the log. so the solution is to reinitialize the publication and it almost always works. once or twice we had to delete and rebuild the publication.

    on db's where we don't run log backups, we do backup log with no_log on a regular schedule and keep the db's in simple recovery mode

  • joeatwork7

    SSC Enthusiast

    Points: 112

    Krishna,

    I thought I'd post another alternative which I didn't see in your article. I've used this in the past, with good results. This method allows me to run it on a live database without causing issues. At the same time, I have had issues where the log file never seemed to decrease after a shrinkdatabase...this has had 100% good results. Of course, the disclaimer would be that you are definitely purging the log file(s), so there is no going back if you didn't back them up.

    USE [AdventureWorks]

    DUMP TRAN [AdventureWorks] WITH NO_LOG

    GO

    DBCC SHRINKDATABASE ('AdventureWorks', 1)

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply