SQL 2008 – Transaction log is full

log-filessql-server-2008tsql

We have some issues with the transaction log files
We have SQL Server 2008

Our situation:

  • Every night, we have many jobs that runs on the server by SQL Agent
  • Some jobs stop because the transaction log for database is full
  • Everything is fine when we run failed jobs in morning

All databases recovery mode is Simple, except 2 databases (but we never get log full on them)

And the log file have the following option:

  • Autogrowth
  • 10 %
  • Unrestricted (some have 2TB)

Disk space is not a problem, the hdd has never exceeded 60%
Data and log have their own hdd

Do you have any idea how to correct the transaction log full problem?

UPDATE
Log File size
msdb ~150mb

master ~2mb

temp ~265mb
model ~2mb
The biggest log file is ~3GB, 3 have ~1GB, and the others are less than 1 GB

But we got log full on a 650mb and on a 27mb log file size

Best Answer

Take a look at the following MSDN article on Troubleshooting a Full Transaction Log (Error 9002)

Related Topic