Job Suddenly Failing....error 1101

[onegurl]

I have a data process on SQL server 2000 that runs daily and has been fine until recently. Logs spit out error 1101 -

"Unable to allocate new page for database 'database name'. There are no more pages available on valid allocation pages. Space can be created by dropping objects, extending the database or dumping the log with no_log."

MS Support documents the error but with a situation that does not match mine. Additionally this database has more than enough space to grow.

Any ideas? Is there a way to manually add pages? thanks in advance for any help!

 

[tlbroadbent]

Do you backup the database regularly? When performing a full or differential backup, does the transaction log get truncated?

What is the size of the log file? Is the Transaction log set to grow dynamically? What is the growth factor? I've found that sometimes the growth factor must be increased so the log and/or database can grow fast enough to keep up with the update process.

Can the log grow to unlimited size? Is there space on the disk for the additional growth?

If you are doing mass updates, I recommend processing and commiting smaller batches rather than doing several hundred thousands or millions of updates in the same transaction. This will help keep the transaction log small.

See thread183-77264 for an example. Check the following link for more info on "batching."

http://www.swynk.com/friends/sjones/Batching.asp

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.