Full Transaction log

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

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

    Tom Walters

  • What about shrinking the database with replication???

  • 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

  • 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.

  • 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.

  • 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.

  • 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.

  • 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

  • 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.

  • 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

  • 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
  • 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

  • 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

  • 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