Solved

Database has reached its maximum size

Posted on 2002-06-30
9
441 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 142

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

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.

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…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

776 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