Home / server / Questions / 156318
In Process
Cloudlucky
Asked: 2010-07-01 06:29:39 +0800 CST

SQL 2008 - Transaction log is full

  • 772

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

log-files sql-server-2008 tsql

2 Answers

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

    • 3

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

    • 1