Why is my log file so massive? 22gb. I am running log backups

Question

I can't seem to figure out the answer. I've seen multiple answers like this: Why Does the Transaction Log Keep Growing or Run Out of Space?

and everyone talks about running back ups on your log file so it shrinks down. I am doing that, but it doesn't shrink anything! I also don't believe I am running any super long transactions.

Server: SQL Server 2008

Recovery Mode: Full

I have a maintenance plan to store 5 days worth of backups. Task 1 backups up the databases with Backup Type Full, Task 2 backs up Transaction logs. Verify backup integrity is checked on both tasks.

My DB's normal .ldf file is 22gb. When I run the above task, the .bak file is 435mb, but the .trn. file is 22gb, same as the ldf. And after successfully running the .ldf doesn't shrink at all, despite everything I've read telling me it should?

What is going on here and why doesn't the log file ever shrink?

I've also tried running this command as mentioned in another answer:

select name, log_reuse_wait_desc from sys.databases

And it says LOG_BACKUP for the db with the huge log file.

Based on an answer below I am confusing allocated with used space. These are my stats for:

For reasons I have no clue why, the initial size was set to 22gb...

Answer 1

You are confusing allocated space with used space. After running the backup use this query to see the difference between allocated and used space.

select file_id
, type_desc
, name
, substring([physical_name],1,3) AS [Drive]
, physical_name
, state_desc
, size / 128 as 'AllocatedSizeMB'
, FILEPROPERTY([name],'SpaceUsed') /128 AS 'SpaceUsedMB'  --Addapted from https://sqlperformance.com/2014/12/io-subsystem/proactive-sql-server-health-checks-1
, (1- (FILEPROPERTY([name],'SpaceUsed') / CAST (size AS MONEY))) *100 AS 'PercentFree'
, growth / 128 as 'GrowthSettingMB'

 from sys.database_files
 order by type_desc Desc, name

You can use the GUI to shrink the log file by changing the 'Initial size'

If you are having troubles shrinking the log, even when it looks mostly empty see my post here

Answer 2

Taking this backup will just backup the data and clear the log. The actual size of the log will need to be shrunk via a DBCC command if you really need to shrink the log. Depending on how often you are backing up your log file it will likely just grow again.

Try running this to see how much actual space on your log is taken up.

SELECT 
    [TYPE] = A.TYPE_DESC
    ,[FILE_Name] = A.name
    ,[FILEGROUP_NAME] = fg.name
    ,[File_Location] = A.PHYSICAL_NAME
    ,[FILESIZE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0)
    ,[USEDSPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0))
    ,[FREESPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)
    ,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100)
    ,[AutoGrow] = 'By ' + CASE is_percent_growth WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -' 
        WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -' ELSE '' END 
        + CASE max_size WHEN 0 THEN 'DISABLED' WHEN -1 THEN ' Unrestricted' 
            ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB' END 
        + CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting!]' ELSE '' END
FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id 
order by A.TYPE desc, A.NAME; 

If you actually have loads of free space available you can run the DBCC SHRINKFILE command in order to get your log file down to whichever size you think it should be.

Edit: You may also want to check DBCC LOGINFO; then you can see any items that are in use by your transaction log file as they will have a status of two.

HOWEVER whatever activity caused you log file to grow in the first place is likely to continue to happen. From the sounds of thinks you're only taking one log backup a day.

What you should be doing is taking multiple log backups throughout the day in between your full database backups. I'd likely recommend starting with hourly and adjust to see ultimately what works best for you. You can either continue doing this via maintenance plans if that's what's comfortable for you. Other wise you could use Ola Hallengren's scripts to set up a maintenance plan. There are a lot of different options to go with and for the most part they're all pretty great as long as you're taking frequent backups.