Post reply

Transaction log file growth

  • June 10, 2013 at 4:34 am

    #278690

    HI everybody.

    We backup our log files every 10 mins. 3 days ago we had an incident where users where complaining of 'timous' or slow/poor response times. We use monitoring software and i have since discovered that our monitoring software had stopped monitoring the server in question so i was not alerted to any issues. There is no info in the error logs other than the usal log every 10 mins of a succesfull backup. I can see that the transaction log file is sized at 2Gb and has CURRENTLY 99.5% free.

    I am looking for a way to see if the file had to auto grow during the reported time of poor performance but unforrtunately the individual log file backups have now been deleted. Is there a way of checking this from a dmv ? Or the first LSN and last LSN which is recorded in the error logs ?

  • June 10, 2013 at 4:42 am

    #1622371

    Easy enough to find the size of the backup files, assuming msdb still has the history:

    SELECT backup_finish_date, backup_size/1024.0/1024.0 AS Backup_Size_MB FROM msdb..backupset

    WHERE type='L'

    AND backup_finish_date BETWEEN 'datetime1' AND 'datetime2'

    Seems a pretty specific thing to be searching for, given vague reports of timeouts and no historic monitoring. Could be anything. Could be nothing to do with the database at all.

  • June 10, 2013 at 4:57 am

    #1622377

    Thanks very much Howard - that is a really useful script. I have just checked the log backups using this info and everything looks good ie no really big transaction log backups for that period.

    Thanks for you help

    Pj:-)

  • June 10, 2013 at 5:16 am

    #1622380

    Howard - Is there a similar way to see if teh datafile grew at the same time ?

  • June 10, 2013 at 5:21 am

    #1622381

    You can check the default trace for both data and log files, autogrow events are logged. The trace may have rolled over though, it only keeps 5 files of max 20MB.

    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

  • June 10, 2013 at 5:22 am

    #1622383

    So if the datafile auto grew it would have showed up in the sql logs ?

  • June 10, 2013 at 5:33 am

    #1622389

    No, it would not have shown up in the SQL error logs, it would have shown up in the default trace.

    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

  • June 10, 2013 at 5:39 am

    #1622390

    Gail - Im sorry - How do i get the data from the default trace ? Are you talking about a profiler trace because i havent been running one ?

  • June 10, 2013 at 6:28 am

    #1622404

    https://www.simple-talk.com/sql/performance/the-default-trace-in-sql-server---the-power-of-performance-and-security-auditing/

    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

  • June 10, 2013 at 7:07 am

    #1622419

    Thank you Gail. Very useful. I dont think i've read about this - unless i've totally missed it. 🙂

  • June 10, 2013 at 12:18 pm

    #1622568

    Right-click on the database in Object Explorer - select Reports - Select Disk Usage report. If there are still records in the default trace then this report will show that information.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 11 posts - 1 through 10 (of 10 total)

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