Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

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

2 Answers 2

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

share|improve this answer
    
ok, so the plan suggestes is to make the DB maintenance plan - Shrink - On Demand and then change the recovery mode into "simple" ? –  Albert Widjaja Apr 15 '11 at 1: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
    
many thanks for the reply. –  Albert Widjaja Apr 18 '11 at 3:39

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

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.