3

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

Cloudlucky
  • 31
  • 4
  • Do you have a maximum size set on tempdb (or any of the other files, for that matter)? This may be your problem. –  Jun 30 '10 at 21:13
  • @Randolph Potter, no maximum size. The autogrowth is unrestricted – Cloudlucky Jul 02 '10 at 14:19

2 Answers2

3

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

Larry Smithmier
  • 418
  • 1
  • 4
  • 7
  • ok, so the plan suggestes is to make the DB maintenance plan - Shrink - On Demand and then change the recovery mode into "simple" ? – Senior Systems Engineer Apr 15 '11 at 01:31
  • 1
    If the simple recovery mode is sufficiently granular for your environment, then you can change to it and reduce the resources used by your transaction log. – Larry Smithmier Apr 16 '11 at 16:59
1

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

lg.
  • 4,579
  • 3
  • 20
  • 20
  • 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 – Cloudlucky Jun 30 '10 at 15:22