Job Failed Because Log File is Full 1

[JohnBates]

hi all,

This is SQL Server 2000 Std.

A critical job failed this morning. This job copies an 11 million row table and has been completing successfully for 2 years. This database is using SIMPLE Recovery mode.

The log file for database 'gm' is full. Back up the transaction log for the database to free up some log space. [SQLSTATE 42000] (Error 9002). NOTE: The step was retried the requested number of times (2) without succeeding. The step failed.

The t-log was 9.5GB after the failure but the disk drive still had 6GB freee space.

My questions are:
1. Is already have set "Unresticted file growth" enabled for the t-log and 100 mb increments. I shrink the database and log before this heavy jobs starts.


2. How does SQL Server determine that the log file is full, even when you have specified "unrestricted growth" and the disk still has 6GB free space?

Thanks very much!
John

 

[ptheriault]

Did you check the tempdb log?

- Paul
- Database performance looks fine, it must be the Network!

 

[ptheriault]

Also,
It is possible that the job is working faster than the log can grow. You can try changing the log growth to 200MG at a time.

- Paul
- Database performance looks fine, it must be the Network!

 

[JohnBates]

Thanks ptheriault.

tempdb itself was 4GB.

Because the message is "The log file for database 'gm' is full" I assume it was the log for the gm database that became "full". Maybe I'm wrong. I've learned that sometimes Microsoft's messages can be misleading.

John

 

[ptheriault]

no problem. One of things I do is I move my tempdb and log to another disk drive so it won't doesn't effect I/O and growth of my other databases.

- Paul
- Database performance looks fine, it must be the Network!