Post reply

Can't Shrink the Transaction Log

  • November 23, 2011 at 11:13 am

    #263382

    I have several transaction Log Files that are huge because Transaction Log Backups were disabled and they are 60-80GB.

    I don't know the history on these servers but I see that there are replication publications on this particular database.

    Any help would be appreciated.

    BACKUP LOG @DatabaseName

    WITH TRUNCATE_ONLY

    DECLARE @LogFileNameVARCHAR(35)

    SET @LogFileName = (SELECT Name FROM sys.database_files WHERE type_desc = 'LOG')

    SELECT @LogFileName AS LogFileName

    DBCC SHRINKFILE( @LogFileName,2)

    Perform Differential before ad after to be safe.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • November 23, 2011 at 11:16 am

    #1412379

    Is there free space in the log files? What's the log reuse wait type?

    http://www.sqlservercentral.com/articles/Transaction+Log/72488/

    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

  • November 23, 2011 at 11:34 am

    #1412389

    How do I verify that there is free space?

    The log_reuse_wait_desc = REPLICATION

    Apparently this means something to the effect of SQL Server waiting for a replication task to finish before it can reuse the log space?

    Thanks!

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • November 23, 2011 at 12:05 pm

    #1412410

    Did you read the article?

    Free space with DBCC SQLPERF(LogSpace)

    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

  • November 23, 2011 at 12:20 pm

    #1412415

    GilaMonster (11/23/2011)

    Did you read the article?

    Free space with DBCC SQLPERF(LogSpace)

    I read it, then I scanned it again prior to asking the question.

    Then I search for it and no matches found.

    I must have missed it. :unsure:

    But I check it and only 12% is used.

    Thanks for the article and the DBCC Command.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • November 23, 2011 at 1:35 pm

    #1412460

    Welsh Corgi (11/23/2011)

    GilaMonster (11/23/2011)

    Did you read the article?

    I read it, then I scanned it again prior to asking the question.

    Then I search for it and no matches found.

    There's definitely a section on what the replication wait means.

    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

  • November 23, 2011 at 4:24 pm

    #1412514

    GilaMonster (11/23/2011)

    Welsh Corgi (11/23/2011)

    GilaMonster (11/23/2011)

    There's definitely a section on what the replication wait means.

    I did not question what the replication wait means and I replied to you on that.

    I saw the replication wait but in your article you do not have the DBCC Command to check the percentage of usage of the transaction log file.

    I mentioned that the file is using approximately 11 Percent. You ask me how much was being used so what is the next step?

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • November 24, 2011 at 2:06 am

    #1412583

    Welsh Corgi (11/23/2011)

    GilaMonster (11/23/2011)

    Welsh Corgi (11/23/2011)

    GilaMonster (11/23/2011)

    There's definitely a section on what the replication wait means.

    I did not question what the replication wait means and I replied to you on that.

    Ok, I assumed that this was a question:

    Apparently this means something to the effect of SQL Server waiting for a replication task to finish before it can reuse the log space?

    You ask me how much was being used so what is the next step?

    Well, nothing's waiting for the log to be freed, so try to shrink it. If it won't, take a log backup and try to shrink again (and that's not a guess, that combo of commands has a specific meaning)

    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

  • November 24, 2011 at 2:29 am

    #1412595

    You can try shrinking it with NO TRUNCATE, which should move all the free space to the end, then shrinking again with TRUNCATE_ONLY to get rid if the free space.

    I've found this tactic to work with particularly recalcitrant log files.

  • November 24, 2011 at 2:39 am

    #1412599

    The NoTruncate and TruncateOnly options on a shrink are solely for data files. They are completely ignored when shrinking a log file.

    From Books Online (DBCC ShrinkFile)

    NOTRUNCATE

    Moves allocated pages from the end of a data file to unallocated pages in the front of the file with or without specifying target_percent. The free space at the end of the file is not returned to the operating system, and the physical size of the file does not change. Therefore, when NOTRUNCATE is specified, the file appears not to shrink.

    NOTRUNCATE is applicable only to data files. The log files are not affected.

    TRUNCATEONLY

    Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent.

    target_size is ignored if specified with TRUNCATEONLY.

    TRUNCATEONLY is applicable only to data files.

    Nothing can move a VLF within a log file ever.

    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

  • November 24, 2011 at 2:57 am

    #1412605

    Argh... Shot down in flames by your sharp eyed skills 🙂

    You're right of course. I have actually only used it against Data files in the past, and now I'm hoist on my own petard of eagerness to answer a question :rolleyes:

    I apologise for possibly sending an innocent questionner off barking up the wrong tree, and promise that in future I'll be more carreful with my answers...:ermm:

    One of my favourite sayings is that "Every day is a school day", and today is no exception :blush:

  • November 24, 2011 at 8:33 am

    #1412726

    GilaMonster (11/24/2011)

    [Well, nothing's waiting for the log to be freed, so try to shrink it. If it won't, take a log backup and try to shrink again (and that's not a guess, that combo of commands has a specific meaning)

    I backed up the Log & tried to shrink and the file size 69 GB.

    Thanks!

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • November 24, 2011 at 8:54 am

    #1412733

    Andeavour (11/24/2011)

    Argh... Shot down in flames by your sharp eyed skills 🙂

    You're right of course. I have actually only used it against Data files in the past, and now I'm hoist on my own petard of eagerness to answer a question :rolleyes:

    Don't worry, that one comes up soooo often.

    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

  • November 24, 2011 at 9:47 am

    #1412763

    GilaMonster (11/23/2011)

    Welsh Corgi (11/23/2011)

    GilaMonster (11/23/2011)

    Did you read the article?

    I read it, then I scanned it again prior to asking the question.

    Then I search for it and no matches found.

    There's definitely a section on what the replication wait means.

    I never doubted that there was a description of relication wait.

    My question was not related to that.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • November 24, 2011 at 1:03 pm

    #1412779

    GilaMonster (11/24/2011)

    The NoTruncate and TruncateOnly options on a shrink are solely for data files. They are completely ignored when shrinking a log file.

    It was my understanding that the NO_TRUNCATE & TRUNCATE_ONLY Options were discontinued in SQL Server 2008 and they are not ignored in SQL Server 2005 and below.

    With SQL Server 2008 it is recommended that you switch to the Simple Recovery Model, SHrink the Log and Switch back from the Simple Recovery Model.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

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