Database seems to grow out of control!

[Maritime]

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

 

[JohnBates]

Maritime,

I found out recently that tr Recovery Model setting can cause excessive growth - the default recovery model when you create a SQL Server 2000 db is FULL. After I changed the settting to Simple, then performed a db shrink, the db was about 1/3 as big.
But Simple may not be the right choice for you, if this db is mission-critical.

I have also learned (all of this the 'hard way') that performing a reindex of several sql server system tables, prior to running the shrink, can reduce the db size.

Maybe some of this will help.
John

 

[Maritime]

Thanks for the feedback, however I neglected to say that I'm running SQL 7.0 So. . . .? I'm thinking of adding some shrink jobs in between the copy table jobs to see if that helps.

Thanks

 

[SQLBill]

(I use SQL Server 2000 and have never used 7.0)

How do you autogrow your database? By percent or MB? I suggest you have it grow by MB.

You might also want to look into changing the recovery mode to SIMPLE as John suggested. When you do large transactions, change the mode to SIMPLE and then do a full backup when the transaction is done, lastly switch it back to Full.

-SQLBill

 

[Maritime]

I have the database configured to grow, by percentages, so I'll try changing that to MB. I don't think that sql 7.0 has an option to change the modes. I'll investigate that though. Thanks again.

 

[SQLSister]

You might also consider doing your processing in batches. Theres an FAQ around here on how to do that. Ah, here it is,
How to Use Batch Size to Speed Mass Updates, Inserts and Deletes
faq183-3141

 

[Maritime]

Thanks everyone.