April 29, 2002 at 2:16 am
A strange problem on production.
we read data from few text files and update to the database everyday as part of a schedule. Daybefore yesterday we received the below error while performing the operation.
[Microsoft][ODBC Text Driver] System resource exceeded.|Error Connecting to database. |[Microsoft][ODBC Text Driver] System resource exceeded.|-2147024882||.
The SQL is on a Cluster and few searches in the MSDN revealed that the problem was Memory not being available. But the System showed more than 2GB of free physical memory. SQl was utilizing 1.3 Gb. We made a workaround for our application to skip this part and continue. The problem again repeated yesterday. I restarted the SQL Server and re-ran the schedule and the problem disappeared. Is there any clue what exactly was wrong and is there any solution other than restart if this repeats.
Jesus My Saviour
April 29, 2002 at 4:15 am
Anything else running on the box? Also how much RAM do you have and how much RAM and VM was SQL using at the time?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 29, 2002 at 4:17 am
I've seen this before but not related to text files. It was a disk space problem.
Check te available disk space for temp files. Also, check if NT event log could be full.
April 29, 2002 at 4:27 am
Nothing else is running on the System except SQL and our application. The Total RAM is 4GB. the Virtual Memory is set to min 8 and max 10 Gb. There is no chance that the system run out of space since the Boot disks are 9Gb mirrored and the SQL installation is on the shared drive. Well the event log is configured for 49984 kb. Is there any temp folders specifically for SQL??
Once the problem was repeated I moved the clusterIP Group and our application to the Secondary node of the Cluster and then re-ran the procedure but still the error continued...any way the only difference was SQL was still running on the Primary node. This was just to check whether the problem was system related of SQL related. This result put me in a confusion and I blindly blamed SQL. In fact restarting SQL solved the problem...
Jesus My Saviour
April 29, 2002 at 5:12 am
Not sure if you did this setting but from SQL BOL /3GB swtch parameter info
Using AWE Memory on Windows 2000
Microsoft® SQL Server™ 2000 Enterprise Edition uses the Microsoft Windows® 2000 Address Windowing Extensions (AWE) API to support very large amounts of physical memory. SQL Server 2000 Enterprise Edition can access amounts of memory approaching 8 GB on Windows 2000 Advanced Server and approaching 64 GB on Windows 2000 Data Center.
Standard 32-bit addresses can map a maximum of 4 GB of memory. The standard address spaces of 32-bit Microsoft Windows NT® 4.0 and Windows 2000 processes are therefore limited to 4-GB. By default, 2 GB is reserved for the operating system, and 2 GB is made available to the application. If you specify a /3GB switch in the Boot.ini file of Windows NT Enterprise Edition or Windows 2000 Advanced Server, the operating system reserves only 1 GB of the address space, and the application can access up to 3 GB. For more information about the /3GB switch, see Windows NT Enterprise Edition or Windows 2000 Advanced Server Help.
AWE is a set of extensions to the memory management functions of the Microsoft Win32® API that allow applications to address more memory than the 4 GB that is available through standard 32-bit addressing. AWE lets applications acquire physical memory as nonpaged memory, and then dynamically map views of the nonpaged memory to the 32-bit address space. Although the 32-bit address space is limited to 4 GB, the nonpaged memory can be much larger. This enables memory-intensive applications, such as large database systems, address more memory than can be supported in a 32-bit address space. For more information about AWE, see the MSDN® page at Microsoft Web site.
Enabling AWE Memory
You must specifically enable the use of AWE memory by an instance of SQL Server 2000 Enterprise Edition by using the sp_configure option awe enabled.
When awe enabled is set to 0, AWE memory is not used, and the instance defaults to using dynamic memory in standard 32-bit virtual address spaces.
When awe enabled is set to 1, AWE memory is used, and the instance can access up to 8 GB of physical memory on Windows 2000 Advanced Server and 64 GB on Windows 2000 Data Center.
When an instance of SQL Server 2000 Enterprise Edition is run with awe enabled set to 1:
The instance does not dynamically manage the size of the address space.
The instance holds all memory acquired at startup until it is shut down.
The memory pages for the instance come from the Windows nonpageable pool, meaning that none of the memory of the instance can be swapped out.
You must carefully manage the memory used by an instance of SQL Server when awe enabled is set to 1. If the instance acquires most of the available physical memory as nonpaged memory, other applications or system processes may not be able to get the memory they need to run. Use the max server memory configuration setting to control how much memory is used by each instance of SQL Server that uses AWE memory. For more information, see Managing AWE Memory on Windows 2000.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 29, 2002 at 5:23 am
Thanks...Antare..
Andy had suggested me /3GB Switch...I am still studying whether we should go for it...and also AWE...
Jesus My Saviour
April 29, 2002 at 5:54 am
Well that would at least explain the 2 Gb's free. Check out what all SQL is doing and looking and task manager how sharp did the memory climb (fast or slow). If fast then use profiler to catch what is going on at those times (sorry cannot be more specific). If slow then use profiler and perfmon to what the main memory and page file looking for times where spikes are happening and compare between the two. Thirdly are you causing any file extreme file growth when you do the imports may need to adjust the growth size per database jump (say you have a growth of 10% and the DB is currently 500mb but will grow 250mb mores, this means you it will calc each change and grow a few times). Try a fixed value for growth (db and log) and use the amount you are sure will grow plus a few MB to prevent more accesses to the drive than needed and to use less memory in the calculation as is fixed. Also look at how this is acting in you log file, make sure is not causing too much growth. And look at rewriting any code that is resource intesive to see if you can utilize memory better. Maybe break the import into pieces if nothing else can be done. Finally, consider what happens with indexes when large inserts are done and consider dropping the indexes until done the add back (clustered first) after the import.
quote:
System resource exceeded
does not always mean memory, could be hard drive contention as well. Hope this gives enough starting points to look.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 30, 2002 at 1:33 am
Thanks a lot Antare..
I am looking out all the solutions explained by you...Well i can try them only if the Problem repeats....
levi
Jesus My Saviour
May 1, 2015 at 2:56 am
Hello.
I seem to be having the same problem, and am hoping you can help me. Unfortunately, I am very green when it comes to SQL Server as most of my db experience is with MS Access. We use SQL server when we are forced to update, do to db size.
My application produces a System Resource Exceeded error during routines which cause extreme file growth. I've reviewed the posts in this thread but don't understand the post related to this problem.
We are using MS SQLServer 2008 R2 which is installed on a Windows 7 machine. I've tried AWE and the switch referenced however the error continues.
The routine which causes the error is very simple.
The first thing the code does is to create a group of records that we want to process. The group can range from 20,000 to 200,000 records. This routine always works flawlessly.
Next, the routine that causes the system resource exceeded error runs. This routine opens
a recordset of all of the selected records, then loops through the recordset one time, updating each record in a manner which certainly causes the database size to grow significantly.
When I use the routine to update 20,000 records, it works every time, without producing any errors.
When I use the routine to update 100,000 records, it fails every time with the system resource error.
The machine running MS SQL Server 2008 R2 has all of the current service packs and is a dedicated machine with 8GB running windows 7 in 64 bit mode although the 32-bit version of SQL Server is installed on the machine.
The workstation is an XPpro sp3 running ms access 2000 and the sql table is linked using odbc.
The domain is a LAN and the controller is Win2003 Server.
Any help will be appreciated greatly.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply