Solved

Database has reached its maximum size

Posted on 2002-06-30
9
443 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

740 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