patternsqlMinor
Why is my log file so massive? 22gb. I am running log backups
Viewed 0 times
whyfilebackups22gbmassivelogrunning
Problem
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:
Recovery Mode:
I have a maintenance plan to store 5 days worth of backups. Task 1 backups up the databases with Backup Type
My DB's normal
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:
And it says
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...
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 2008Recovery Mode:
FullI 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.databasesAnd 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...
Solution
You are confusing allocated space with used space. After running the backup use this query to see the difference between allocated and used space.
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
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, nameYou 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
Code Snippets
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, nameContext
StackExchange Database Administrators Q#234221, answer score: 8
Revisions (0)
No revisions yet.