Database seems to grow out of control

[Katyan]

My database has tables that change each day. Jobs are scheduled to delete and copy from an AS/400 into these Databases. Most times these tables are copied without any problem, but occasionally the database increases in size, filling up the Disk. All other jobs then fail for a lack of space. Or you get an error message on the failed jobs saying that the transaction log is full. (I have truncate log on checkpoint selected.) On checking the physical disk space the disk has no free space and the database size has ballooned.

Most times, shrinking the Database frees up some space, but not much. A few hours later most of the space is released. How can I prevent this from happening? I have the automatically grow file option selected, could this be the problem? And if so, how should this be configured to prevent my databases from "over inflating?"

Thanks

[rmiao]

Is db log file on this disk? How big the log file is? What's sql version? Do you enable db option 'truncate log on checkpoint'? If not, do you backup log?

[Katyan]

The log file IS on the same disk as the database. I do have truncate log on checkpoint selected. The log is currently 185MB (the database is also at an acceptable size right now- 2GB, but like I said it can grow up to 7GB). I do not backup the log file. I'm running SQL 7.0. Thanks.

[skhanal]

It could be caused by long running transaction. If a transaction which started earlier does not commit, even though other transaction which started after it committed, sql server can't truncate log.

Try to break your data pull job into multiple smaller jobs with frequenct commits.

[Katyan]

What do you mean by less frequent commits. I do have the data copy broken into many jobs, but according to what you're saying I may still have to break them up even smaller.

[rmiao]

Maybe. What's log size when the db grows to 7gb?

[Katyan]

I don't remember right now. But I'll check when it happens again. Hopefully if I can break up the jobs this will not happen again.

[MAK]

when the job runs. run another job that can do the "checkpoint" every 1 min.