SQL Memory and External Data

The Problem

I have been having problems importing data into SQL Server from Excel file for nearly a year. I would perform an OpenDataSource command in SQL to open an Excel 97-2003 file and import the data into a temporary table. The problem is that it would work once and would then return the following error on every attempt after that, until the SQL Server service was restarted.

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error. Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

You can also get the following error when performing queries.

Msg 10314, Level 16, State 11, Line 1
An error occurred in the Microsoft .NET Framework while trying to load assembly id 66669. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
System.IO.FileLoadException: Could not load file or assembly 'nexgen database, Version=1.0.3945.15002, Culture=neutral, PublicKeyToken=a80b074634e01a5e' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)
System.IO.FileLoadException:
   at System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
   at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
   at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
   at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
   at System.Reflection.Assembly.Load(String assemblyString)

I submitted several forum posts over this time trying to elicite some sort of information on how to fix this problem. Here are a couple of the forum posts I made to try to get this problem fixed.

• http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/aef8303d-06c0-4c52-b06a-9f1bf3705664
• http://social.msdn.microsoft.com/Forums/en/sqlsetupandupgrade/thread/f613520f-4ba0-4597-8b93-8f3e1774a1fd

Attempts to Fix It

However, all  of my attempts at getting this resolved always resulted in either a ton of people replying that they’re also experiencing this issue or links to the same old articles. Here are some of the things that MS KB Articles and other resources around the Internet recommended along with some things I tried in order to resolve this problem. There is a mess of other things I tried as well, but I don’t want to have 3 pages of things that don’t work listed here.

• Make sure the user that the SQL Server service is running as has read and write access to the temp and tmp environment variable folders.
• Try importing a different file.
• Try a file that’s local to the SQL Server.
• Try setting up a Linked Server for the file.
• Run ASP.NET, SQL Server, memory, file system, registry, process and service traces to try to track down the problem.
• Install SQL Server 2005 SP3, which according to this article is supposed to fix the excessive open temp tables problem.

Recently I received a message from someone named Jim Cheong. He posted a comment through my first blog post on this site asking whether I had solved the problem in this MSDN forum post. After talking with him he said that he was experiencing the same issues and had possibly come across a fix for it, which he was currently trying out.

The Root of the Problem

He said that the MemToLeave area might be to blame and pointed me towards this SQL Server Central article. I ran this script they have on their site to determine the amount of total available memory and largest free size. I found that I only had 18MB of available memory and the largest free block was 11MB.

With VAS_Summary As (
     Select Size = VAS_Dump.Size,
            Reserved = SUM( Case(Convert(int, VAS_Dump.Base) ^ 0)
                                When 0
                                Then 1
                                Else 0
                            End),
            Free = Sum( Case(Convert(int, VAS_Dump.Base) ^ 0)
                            When 0
                            Then 1
                            Else 0
                        End)
     From (Select   Convert(varbinary, Sum(region_size_in_bytes)) [Size],
                    region_allocation_base_address [Base]
           From     sys.dm_os_virtual_address_dump
           Where    region_allocation_base_address <> 0x0
           Group By region_allocation_base_address
           Union
           Select   CONVERT(varbinary, region_size_in_bytes) [Size],
                    region_allocation_base_address [Base]
           From     sys.dm_os_virtual_address_dump
           Where    region_allocation_base_address = 0x0) As VAS_Dump
           Group By Size)
Select     SUM(convert(bigint, Size)*Free)/1024 As [Memory: Total Avail (KB)],
           CAST(max(size) as bigint)/1024 As [Memory: Max Free (KB)]
From       VAS_Summary
Where      Free <> 0

According to testing by Jim, he found that the OpenDataSource and OpenRowSet commands need approximately 71MB of free memory in order to run. This means that I was far below the necessary amount in order to run. So, the imports would fail until I restarted the SQL Server service, thus freeing up this memory and allowing me to do an import (maybe 2) before it would start failing again.

The Real Fix

As the SQL Server Central article states though, allocating more memory to the MemToLeave pool isn’t as easy as just setting the SQL startup switch. Whatever additional memory you allocate to the MemToLeave pool is taken from the Buffer Pool (BPool), which is SQL’s main operating memory. For this reason, reducing the amount of memory in this pool can have some very undesirable side effects. So, set this responsibly, do testing out of production first and get ready to set it back if things don’t work out the way you hoped, but here is how you allocate more memory to the MemToLeave pool.

1. Open SQL Server Configuration Manager.
2. Select the SQL Server Services folder in the left pane.
3. Right-click the SQL Server (MSSQLSERVER) service in the right pane.
4. Click Properties.
5. Click the Advanced tab in the properties dialog that pops up.
6. Add “-g512;” to the front of the value for parameter “Startup Parameters”.
7. Click OK.

Summary

So, Jim and I setup processes to track how much total available and free memory we had over time. At the end of a week we compared notes. Both of us had well over the necessary amount, even after a week and neither of us had experienced the problem again. For me, it’s been more than 2 weeks now and I have not had to touch the SQL Server service at all. This has been a major life saver and I am eternally greatful to Jim for spotting this solution!