Thursday, February 3, 2011

SQL 2008 - Transaction log is full

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

  • Check transaction log of system db (msdb, master, temp, etc)

    Cloudlucky : 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
    From lg
  • Take a look at the following MSDN article on Troubleshooting a Full Transaction Log (Error 9002)

0 comments:

Post a Comment