• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 459
  • Last Modified:

Database has reached its maximum size

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
kiddiec
Asked:
kiddiec
1 Solution
 
kpkpCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
mironCommented:
break your job in 2, update half users in first, half users in second jobs.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
kiddiecAuthor Commented:
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
 
mironCommented:
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
 
mmcmillenCommented:
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
 
kiddiecAuthor Commented:
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
 
SpideyModCommented:
PAQ'd and points refunded.

SpideyMod
Community Support Moderator @Experts Exchange
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now