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...
everyone talks about running back ups on your log file so it shrinks down
- no, nobody says that, and log backups will never shrink a file. They say running backups frequently should help prevent it from growing, since the space inside can be reused once it has been backed up. Sometimes the log space gets used faster than your backups run. If this happens often, shrinking just to grow again is pointless, just leave them large. If this is due to a known, abnormal event and you've put something in place to prevent it happening again, that is about the only time I would advocate any shrinking. – Aaron Bertrand Apr 8 '19 at 21:13