Transaction Log Woes

  • I'm having some difficulties with transaction logs filling.

    Every hour, we have an SSIS package that takes transaction log backups, based on the following code: -

    select 'BACKUP LOG ['+name+'] TO ['+name+'] WITH NOFORMAT, NOINIT,

    NAME = N'''+name+'-Transaction Log Backup'+CONVERT(VARCHAR(12), GETDATE(), 102)+' '+CONVERT(VARCHAR(8), GETDATE(), 108)+''', SKIP, NOREWIND, NOUNLOAD, STATS = 10'

    from sys.databases where name not in ( 'Adtec','Tempdb') and recovery_model_desc = 'FULL'

    At the end of the day, the SSIS package does a backup of the databases, based on the following code: -

    select 'BACKUP DATABASE ['+name+'] TO ['+name+'] WITH '+CASE WHEN Datepart(dw,getdate())<>2 then 'DIFFERENTIAL,' else '' end+' NOFORMAT, NOINIT,

    NAME = N'''+name+'-'+CASE WHEN Datepart(dw,getdate())<>2 then 'Differential' else 'Full' end+' Database Backup'+CONVERT(varchar(12),GETDATE(),102)+''', SKIP, NOREWIND, NOUNLOAD, STATS = 10'

    from master..sysdatabases where name not in ( 'Adtec','Tempdb',CASE WHEN Datepart(dw,getdate())<>2 then 'Master' else ''end )

    Even with this in place, our transaction logs fill extremely quickly then auto-grow to the point of running out of disk space on the separate hdd that stores the transaction logs.

    When I check for log_reuse_wait, I rarely get anything other than either "Nothing" or "Log_Backuo" as a response for each database.

    I'm at a complete loss here. Any help would be greatly appreciated 🙂


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Run the log backups every 10 minutes (maybe even less).

  • Ninja's_RGR'us (5/19/2011)


    Run the log backups every 10 minutes (maybe even less).

    I'm not sure that would solve the issue I'm having (I'm not saying it won't, just that I'm confused as to why it may be necessary 🙂 ).

    The reason is that after 3pm, several of our databases are barely used. To qualify, I mean that barely any data is loaded/updated/deleted, but they are still selected from.

    So, with my little knowledge of transaction logs, I'd have thought that after 3pm we'd have our transaction logs back at a high amount of free space. This isn't the case, which is why I'm confused.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • When the backup is taken, the space is marked available for reuse. so the log doesn't need to grow.

    You can schedule it more than once... maybe every 10 minutes from 9 to 5 then every hour from 5 to 9.

    But the point is that it doesn't matter. The log backups will only containt 100K of so of log headers if they are empty so that won't fill up the drives either.

    One more thing is that if you have reindexing jobs it can fill up the logs REALLLLLY fast. So I'd make sure that this is not the issue here.

  • Ninja's_RGR'us (5/19/2011)


    When the backup is taken, the space is marked available for reuse. so the log doesn't need to grow.

    You can schedule it more than once... maybe every 10 minutes from 9 to 5 then every hour from 5 to 9.

    But the point is that it doesn't matter. The log backups will only containt 100K of so of log headers if they are empty so that won't fill up the drives either.

    One more thing is that if you have reindexing jobs it can fill up the logs REALLLLLY fast. So I'd make sure that this is not the issue here.

    The reindexing thing was the previous problem (actually, we were reindexing and recompiling statistics), that was spotted about a month ago so we changed it 🙂

    I'll look into changing the schedules for during busier periods and let you know. Thanks.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • We've solved our issue, it was a D'OH moment 😉

    Basically, the code I posted above is an older version of what had been deployed to the server (must've been changed when I wasn't looking :hehe:). The newer code was malfunctioning, meaning it wasn't taking a transaction log back-up which meant we were having to do it manually.

    We've fixed the offending code and it all seems to be running smoothly now.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Time to track the guilty party! 😉

  • Ninja's_RGR'us (5/19/2011)


    Time to track the guilty party! 😉

    Not too difficult - there are two of us that work on the database servers and it wasn't me. . . 😀


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • skcadavre (5/19/2011)


    Ninja's_RGR'us (5/19/2011)


    Time to track the guilty party! 😉

    Not too difficult - there are two of us that work on the database servers and it wasn't me. . . 😀

    Time to wack the guilty party (just behind the head is enough... no need for permanant dammage :-D)

  • Ninja's_RGR'us (5/19/2011)


    skcadavre (5/19/2011)


    Ninja's_RGR'us (5/19/2011)


    Time to track the guilty party! 😉

    Not too difficult - there are two of us that work on the database servers and it wasn't me. . . 😀

    Time to wack the guilty party (just behind the head is enough... no need for permanant dammage :-D)

    With a SQL Server Administration book, just for added effect.

    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
  • GilaMonster (5/19/2011)


    Ninja's_RGR'us (5/19/2011)


    skcadavre (5/19/2011)


    Ninja's_RGR'us (5/19/2011)


    Time to track the guilty party! 😉

    Not too difficult - there are two of us that work on the database servers and it wasn't me. . . 😀

    Time to wack the guilty party (just behind the head is enough... no need for permanant dammage :-D)

    With a SQL Server Administration book, just for added effect.

    Yup, right over the full encyclopedia of your choice 😀

  • Check this article's.

    Truncating and shrinking the transaction log file

    Shrinking log file script

    I Hope this will help others.

    Edited: to correct the grammar.

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • muthukkumaran (5/20/2011)


    Check this article's.

    Truncating and shrinking the transaction log file

    Shrinking log file script

    I Hope someone will help

    So the log file is too small and you want him to shrink it?!?!

    Are you sure you posted in the correct thread?

  • muthukkumaran (5/20/2011)


    Check this article's.

    Truncating and shrinking the transaction log file

    Shrinking log file script

    I Hope someone will help

    It'll help me in making money, I can charge to fix the damage that it causes.

    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
  • GilaMonster (5/20/2011)


    muthukkumaran (5/20/2011)


    Check this article's.

    Truncating and shrinking the transaction log file

    Shrinking log file script

    I Hope someone will help

    It'll help me in making money, I can charge to fix the damage that it causes.

    🙂 Definiitly gail you only corrected the misinformation.

    http://www.sqlservercentral.com/Forums/Topic1078551-1550-4.aspx

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

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

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