Solved

Database has reached its maximum size

Posted on 2002-06-30
9
442 Views
Last Modified: 2011-09-20
I am running a procedure from an Access front end to update some 19000 + memberships in a sql 6.5 database.

At about 16200 renewals the program falls over and states that the database has reached it's maximum size.  When I check the database, master and temb db's all appear to have pleant of room for expansion... Any suggestions would be most welcome
0
Comment
Question by:kiddiec
9 Comments
 
LVL 4

Expert Comment

by:kpkp
ID: 7120616
is it a sqlserver error - can you post the exact error message?  Also the result of an EXEC sp_helpdb?

It's possible that the database get full after 16200 renwals, then rolls back the transaction - creating free space again.  It could also be the transaction log itself filling up, although the error message would refelct that usually.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 7121167
I agree with kpkp that the transaction log space is probably filled. You need to either back it up or truncate the transaction log.
CHeers
0
 
LVL 9

Expert Comment

by:miron
ID: 7121283
break your job in 2, update half users in first, half users in second jobs.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 2

Author Comment

by:kiddiec
ID: 7121310
I would like to be able to break it into two, unfortunately the procedure runs overnight, as it is quite data intensive, and therefore there is no way to close it down and restart it automatically.

I have noticed that closing the front end and then opening it again allows the renewal process to complete the last 3000+ memberships with no problem.

This leads me to believe it is a log size or possibly a temp DB problem, but then the error being returned would be different
0
 
LVL 9

Expert Comment

by:miron
ID: 7121417
could you set an autoincrement field on table and have two jobs scheduled in a script one after another such that first compute on users with increment field
from 1 to max increment / 2
second on max increment / 2 + 1 to max increment

you might also want to take a look at size of hush buckets, I bet it is set to 2 ^ 17
0
 
LVL 2

Expert Comment

by:mmcmillen
ID: 7136020
what are the settings on the tlog growth?   You should allow it to grow, but it sounds like truncation needs to take place after .
0
 
LVL 2

Author Comment

by:kiddiec
ID: 7138311
After a considerable amount of investigation I have discovered it is the front end (Access.mdb) that is growing too large, not the back end.

The coding creates and destroys a temporary table evertime a specific piece of code is called.  This wasn't a problem originally as the code made use of a cursor to loop through the 19000 records, and the code which creates and drops the temporary table was called once at the end of the process.

Due to problems with tracability, the coding was changed to pass in a record ID one at a time, thus the table was created and destroyed each time.

As the table simply held the success or otherwise of the procedure, I recoded it to call the stored procedure direct and not use a table, but local variables.  Thus the problem has now gone away.

Thanks to all who commented
0
 

Accepted Solution

by:
SpideyMod earned 0 total points
ID: 8492207
PAQ'd and points refunded.

SpideyMod
Community Support Moderator @Experts Exchange
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question