Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Database has reached its maximum size

Posted on 2002-06-30
9
Medium Priority
?
450 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

636 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