none
SQL Transaction File not Shrinking after backup

    Question

  • I have a database that is 21 GB in size and has a log file that is about the same size. The recovery model is set to Full and I do nightly Full backups and Hourly Transaction Log backups. My understanding is that the Hourly Transaction Log backup will keep the size of the Transaction Log in check. Also if the database needs to be restored at most we would lose the previous hour.

     

    In All Tasks -> Shrink -> Files the Log file indicates Available Free Space is 99%. My feeling is that the problem is with the Inital Size of the Transaction Log. It is currently set to the same size as the Transaction Log. Whenever I try and manually reduce the size of the Initial Size (Right-click on database -> Properties -> Files option) the number that is entered gets reverted back to the much larger size after selecting OK.

     

    I was able to manually shrink the Transaction Log by using the below command. This set the Initial Size to 5 MB. I ran the command on Sunday and 2 days later the Transaction Log file is at 20 GB and the Initial Size is 20450 MB.

     

    DBCC SHRINKFILE(MyDatabase_Log, 1)
    Go
    BACKUP LOG MyDatabase WITH TRUNCATE_ONLY
    Go
    DBCC SHRINKFILE(MyDatabase_Log, 1)

     

    Any suggestions on what can be done to keep a more manageable Transaction Log File?

     

    Tuesday, December 02, 2008 7:04 PM

All replies

  • If the Transaction Log consistently grows to 20GB with hourly log backups then look at the sizes of your log backups.  If they are growing incrementally in size up to 20GB then you may be doing a Log backup with the NO_TRUNCATE option, which will not clear the log space for reuse.  If your Log backup files are of different sizes, and then you have a 20GB log backup, then you have a long running process or transaction that is causing excessive logging.  I just recently wrote a blog post coving this subject:

    The Database Transaction Log - Part 1: Managing Size

    If the log consistently grows to 20GB and then stays there, then that is the size the log should be, and you shouldn't be shrinking it.  Doing so will only cause physical fragmentation on the disks holding the files.

    • Proposed as answer by Pablo Gil DBA Thursday, June 14, 2018 3:16 PM
    Tuesday, December 02, 2008 8:38 PM
    Moderator
  • Hello, you can do a transaction log's backup and check the minor VTL used and shrink transaction log. I have a script that can do it, but I can't attache the file script.

    Thursday, June 14, 2018 3:37 PM
  • Hi

    the question is about 10 years old......

    ;-)

    Friday, June 15, 2018 5:57 AM
  • Here is the quick wway to shrink transaction log file. Go through it:

    http://www.sqlserverlogexplorer.com/shrink-transaction-file/

    Monday, June 18, 2018 1:25 PM