0

My SQL Server 2005 database is having a giant transaction log. The MDF file is 11MB. The log is 1.5GB. Shrinking didn't release much space (9%). I already killed all my connections so I guess uncommitted transactions can't be the problem here.

Any idea what can cause this? How to resolve it? (backup/restore maybe?) And how to avoid this in the future?

Koen
  • 103
  • 2

1 Answers1

0

You most likely have the database recovery model set to "Full" and you've never taken a backup of the transaction log, so it contains the log of every single command ever executed on that database.

If you're not taking backups, or only taking Full backups, switch the recovery model to Simple. The log file will not shrink, but neither will it grow any further.

Mark Henderson
  • 68,823
  • 31
  • 180
  • 259
  • Seems right. It was set to full. Changing it gave me the free space back after shrinking... – Koen Jan 17 '12 at 10:30
  • The correct way to avoid this in the future is to periodically backup both the log and the dababase. There are reasons to choose Simple Recovery over Full Recovery, but avoiding backups is NOT one of them. – adaptr Jan 17 '12 at 12:02
  • We do have weekly full backups and daily diff backups. I guess that's enough with simple recovery? – Koen Jan 17 '12 at 12:15
  • Of course. Your original response made it seem as if no backups were done; backing up from Simple is perfectly fine as long as you know what its implications are (i.e. no PIT restore, no crash recovery, etc.) – adaptr Jan 17 '12 at 12:32
  • Technically, the log will still grow if regular backups are not taken and the log has an auto-growth setting configured. It'll just take a little while to fill back up to the 1.5 GB that it's currently at and hit the next auto-growth point. – squillman Jan 17 '12 at 14:17